The short story is that you need to create a system module at the daily level using the weekly calendar. From there it's a matter of mapping - which some call a calculation module. See if the article above helps. If not, I can give you more specifics.
Re: Adust daily figures to preserve monthly totals while switching from a weekly to a regular calend
Thank you for your quick answer and for sharing this article in advance.
I assume that I need to run this "adjusted sales" calculation in a Month time scaled line item, and then use that figure in my orange column as a MONTHVALUE("adjusted sales") ?
Correct me if I am wrong, but your method seems to be going from a month level to another month level, and doesn't seem to take into account the daily values. For instance, if the month ends a Friday, and there are no sales on Saturday and Sunday, there should be nothing moved to the next month. How do you handle that?
If you implement @JaredDolich 's solution I think you should be able to get the expected output. You may have to tweak it as per your requirement considering you are looking at daily timescale.You can change the dimensionality of the module and for few line items where you are pulling the daily Sales
One query on Planning Month to Gregorian Month Conversion article - Is there a specific reason that we are keeping month values to be an input field in the system module. There can be cases where planning spoke model will just have one year of a time scale and imagine what happens when there is yearly rollover process.This field has to be maintained every year.
Now we can argue that we could add Time Range to the system module for multiple years and set it up for all years but that still comes under maintenance. Here is what I was thinking - If we had created one more list to maintain Fake months along with the Codes system could have easily populated the month value irrespective of the year hence removing the maintenance.
Great article and it is surely going to help people who want to see numbers in planning month vs book month
Good query. The conversion from 445 to Gregorian happens at the daily level, so you should be good there.
The trick is to determine the right allocation. In my example I divide by 7 and assume each day is equal which we know that is unrealistic. Ideally, you will have a % to total by day of week to better allocate the proportions.
The other nuance is what to do with the first and last month. Ideally, the entire year is preserved. Meaning if the 445 calendar year adds up to $1 million then the Gregorian Calendar year adds up to $1 million. Two issues with that:
445 calendar is 52 x 7 = 364 days; whereas, Gregorian is 365 days (or, 366 depending on if there's a leap year).
445 every 5 to 6 years will add an additional week, or 53rd week to make up for the missing day each year.
So it comes down to the level of complexity you're willing to resolve. A perfect solution will require a bit more work; whereas, a "close enough" solution can be managed rather quickly with some simple mapping as described in the article.
You're on the right track. I happen to agree that the daily allocation is necessary. I suppose you can further simplify it by manually entering the monthly allocations and bypass days altogether. That would guarantee that your calendars at the annual level would reconcile since you'll need to make sure what you take away from a month is given back to another.
A faked out month would work but I suppose the trade off is that you can't use any of the built in time functions.
But to your point, using the calendar or time range, of course, means that we've added an administrative task for someone. But, some good news, is that there's no hard-coding so the only admin task would be to move the calendar forward or extend it.
I finally found another technique that solved my issue, using the "cumulate" formula, and resetting it at every month change.
It looks slightly dirty, but it happens to work fine and achieve the desired result of using my daily figures in the most accurate way possible.
So here are the line items that I ended with:
In a system module at the "Day" level:
- mid-month date => using the 15th of the month assures me to always find the proper Gregorian calendar month for that day (should work well whatever your time settings).
- start => getting the first calendar day of the Gregorian calendar month
- end => getting the last calendar day of the Gregorian calendar month
- calendarMonth => Getting the Gregorian calendar month of the date, as a Time Period.
In a calculation module, at a Day level also:
- netSalesByDay => the input, which are the sales quantities, for every day of the 445 calendar.
- monthlySalesOver445Calendar => the monthly total of the 445 calendar sales, for the assigned Gregorian calendar month of each day
- cumulatedSalesOverGregorianCalendar => cumulates the daily sales, but resets every time we change calendar month.
- monthlySalesOverGregorianCalendar => returns the cumulated sum for the last day of the assigned Gregorian calendar month of each day, which corresponds to the total monthly sales of the Gregorian calendar month. (the orange column of the initial screenshot)
- adjustedNetSalesByDay => returns the adjusted daily figure, proportionally distributed according to the monthly sales (the green column in the initial screenshot).