topic Re: Calculating future rolling average in DAX Commands and Tips

Calculating future rolling average

JanCab — Tue, 29 Mar 2022 19:04:58 GMT

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!

Re: Calculating future rolling average

Jihwan_Kim — Wed, 30 Mar 2022 03:31:02 GMT

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.

Re: Calculating future rolling average

JanCab — Wed, 30 Mar 2022 07:40:38 GMT

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.

Re: Calculating future rolling average

JanCab — Mon, 04 Apr 2022 19:39:48 GMT

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!

Re: Calculating future rolling average

Anonymous — Mon, 06 Feb 2023 08:36:06 GMT

What is the solution for this?

Is exactly what I need!

Thank you!