Calculating future rolling average

[JanCab]

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! 

[Anonymous]

What is the solution for this?

Is exactly what I need!

Thank you!

[Jihwan_Kim]

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]

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! 

[JanCab]

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.