topic Re: Rolling 3 months Average in DAX Commands and Tips

Rolling 3 months Average

Anonymous — Thu, 25 Mar 2021 21:15:45 GMT

Hi everyone.

I have encounter an issue on my report and need your help.

I need a calculation of Trend and it's definition is:

Average of the last 3 months of the month selected.

If my slicer is selected for example:

Jan/2021 the Average should be from Nov/2020 + Dec/2020 + Jan/2021(Blue selection)

Feb/2021 the Average should be from Dec/2020 + Jan/2021 + Feb/2021 (Orange selection)

My slicer of the month comes from my COD_Calendar table

But my data table has data from 01/2020 till 02/2021

Can anyone please help me with these

Re: Rolling 3 months Average

amitchandak — Fri, 26 Mar 2021 09:17:28 GMT

Anonymous , Try a measure like this with help from date table

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),-1),-3,MONTH))

Re: Rolling 3 months Average

ERD — Tue, 30 Mar 2021 07:10:01 GMT

Hello Anonymous ,

To get the average value for the last 3 months (starting from the chosen one in slicer), you'll need to have a proper calendar table (including dates for 2020 and 2021 upon the example) and the next measure:

EDITED upon the next comment

Trend = IF(HASONEVALUE(Calendar[Date]), CALCULATE( AVERAGE(DataTable[Value]), DATESINPERIOD( Calendar[Date], SELECTEDVALUE(Calendar[Date]), -3, MONTH) ), AVERAGE(DataTable[Value]))

Re: Rolling 3 months Average

Anonymous — Mon, 29 Mar 2021 22:40:04 GMT

Hi ERD

Your usage of the time-intel functions is incorrect and will often yield wrong values. Please refer to this guide to understand when you can correctly use them: DATESINPERIOD – DAX Guide. A hint would be: You cannot use time-intel functions with fact tables. You have to have a proper date/calendar table (dimension) to be safe.

Re: Rolling 3 months Average

ERD — Tue, 30 Mar 2021 07:08:35 GMT

Thanks for the hint! Edited previous advise.