Forcasting Last 90 Days

[Bryanna]

Hi,

We are trying to forecast forward how many cases we will ship based on the Past 90 days excluding weekends and applying a weighted percentage increase based on what time of the month we are in. The first third is a 1.0 multiplier second 1.05, Third 1.12.  

Below is a screenshot of the table and the output along with the formula that we are using. Currently the issue is its only looking at the the specific date for the past 90 days but its not accounting for the weekend.

Volume Forecast =

VAR

    MonthlyLY = Calculate([Customer Cases Shipped],DATEADD('Calendar Table'[Date],-1,MONTH))

    VAR

        MONTHLY2 = CALCULATE([Customer Cases Shipped],DATEADD('Calendar Table'[Date], -2,MONTH))

        VAR

        MONTHLY3 = CALCULATE([Customer Cases Shipped],DATEADD('Calendar Table'[Date], -3,MONTH))

        RETURN

        DIVIDE(MonthlyLY + MONTHLY2 + MONTHLY3 , 3, 0)

[lbendlin]

 excluding weekends and applying a weighted percentage increase based on what time of the month we are in. 

That is guaranteed to end in tears and frustration. Weeks and months are not compatible. Please explain what a third is for a month like August  with  31 days.

And you haven't even mentioned holidays yet.

[Bryanna]

Hi,

The months are broken down by date so < 11, 1 < 22 ,2 and > 22, is 3..if we could take holidays into account as well that would be great but it wasnt brought up in the initial ask. 

So youre saying the best way to do a forcast is month to month and not week to week?

[lbendlin]

no, I would rather do it by working days.