Forum Discussion

Bryanna's avatar
Bryanna
Icon for Helper II rankHelper II
3 years ago

Forcasting Last 90 Days

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)

 

 

3 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    3 years ago
     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's avatar
      Bryanna
      Icon for Helper II rankHelper II
      3 years ago

      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?