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 val...
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!