Rolling 3 months Average

[Anonymous]

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

[ERD]

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]))

[Anonymous]

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.

[ERD]

Thanks for the hint! Edited previous advise.

[amitchandak]

@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))