Forum Discussion

JanCab's avatar
JanCab
Frequent Visitor
4 years ago

Calculating future rolling average

Hello,

 

I would like to ask you for your help with a DAX formula which would calculate value of future unit sales, based on rolling average of previous 12 month.

 

See below - there are blue values as history and red ones as rolling average.

 

 

I was able to calculate rolling average for historical values but not for future - basically from already calculated values, as the average is rolling.

 

Note - I do have a calendar table in place which is related to sales data.

 

Hope it makes sense.

 

Thank you very much! 

 

 

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    I am not sure if I understood your correctly, but I think the numbers are showing 14 months rolling average.

    I tried to solve this in Power Query.

    Please check the attached file. I do not know how your calendar table looks like, but I hope this helps to provide an idea.

    Thanks.

    • JanCab's avatar
      JanCab
      Frequent Visitor
      4 years ago

      Hi,

      Thank you very much for the sample file and solution. You calculation is OK, but I would need something like this preferably in DAX, if it is possible... 

      Thank you. 

       

    • JanCab's avatar
      JanCab
      Frequent Visitor
      4 years ago

      Dear all,

       

      Let me update the topic, as I slightly move on, but still without solution. 

       

      To recap - I have been trying to calculate forecast of future sales based sum of rolling average of last 12 months in DAX. Please find below a screenshot from an Excel calculation (formulas + results) -> blue is history, red is forecast:

       

       

      I put together a formula, which calculates average but slightly incorrectly - I do not know, what the problem is...:

       

      Forecast 2 =

      VAR LastDateSale = [Last_date]
      VAR _MONTHRANK = CALCULATE(MAX('Date'[Rank]),FILTER(ALL('Date'),'Date'[Date] = LastDateSale))
      VAR Rolling12_Months_AVG =
      CALCULATE( AVERAGE('Tab_pro_test'[Sold_Units]), DATESINPERIOD ( 'Date'[Date], LASTDATE ( 'Date'[Date] ), -12, MONTH ))

      return
      if (
      SELECTEDVALUE('Date'[Rank])>= _MONTHRANK,
      CALCULATE(Rolling12_Months_AVG,'Date'[Date] = LastDateSale))

       

      The PBIX file is saved here: https://file.io/buLgvtZ1Asmi 

       

      Thank you very much for any hint! 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    What is the solution for this?

    Is exactly what I need!

    Thank you!