Adust daily figures to preserve monthly totals while switching from a weekly to a regular calendar


Hello community,


I am trying to adjust my daily sales forecast so that the monthly calendar total matches the monthly "weekly-calendar" total, while keeping my seasonality as close to reality as possible.


A simple picture will tell more than 100 words.


2020-05-14 23_03_10 - Base.png


.Spreadsheet here:


I am trying to get the green column 'resultNetSales'.

In order to do that, I need the orange column 'sum of daily sales over calendar month' to compute the sum of all netSalesEuroByDay that have the same calendarMonth as the current day.


In Excel, I would simply use the " =SUMIF($F$6:$F,F8,$D$6:$D) " formula.

I would have expected the formula " netSalesEuroByDay[SUM: calendarMonth ] " to give me that result, but it keeps throwing a horrible error:

2020-05-14 23_10_18 - Base.png


Any idea on what formula to put in my orange column?

Or another way of achieving the result column differently?


Thanks a lot,


Best Answer

  • fxlepoutre
    Answer ✓

    Hello @JaredDolich ,


    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:

    2020-05-19 15_15_48 - Base.png


    - 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:

    2020-05-19 15_22_06 - Base.png

    - 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).


    Hope it can help someone else.





  • @fxlepoutre 

    Great question - one that is asked a lot.

    Next week, this article is scheduled to be published which converts a 445 calendar to a Gregorian.


    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.


  • Hello Jared,

    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?



  • Misbah



    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


  • Misbah



    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



  • @fxlepoutre 

    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:

    1. 445 calendar is 52 x 7 = 364 days; whereas, Gregorian is 365 days (or, 366 depending on if there's a leap year).
    2. 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.

    Hope that helps. 

  • @Misbah 

    I love how you think! 

    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.

    Pros and Cons. You're absolutely right though - 

  • Misbah



    I don't think I articulated things pretty well. Apologies! I was not talking about changing the entire dimensionality of the module from Native to Fake time scale Here is what I was talking about.


    It is mentioned that this is ok to keep "Month Number" Line item a manual field. I have listed out my reason why it might not be ok when there is a solution available with you



    Solution to this:

    Step 1: Create a list, Insert numbers from 1-12 and mention your months in the code of the list. It can be as per requirement but I am just keeping 1 as Jan and 12 as Dec

    Step 2: Create one line items in the SYS Module  and call it Months without Year

                Months without Year = LEFT(NAME(ITEM(TIME)),3)

    Step 3: In your existing Month Number Line Item write the formula

               Month number = FINDITEM(TIME, Months without Year)


    This will ensure that we don't have to maintain this line item every year all the time.