Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Rolling 3 months Average

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 slice...
average
Need Help
ERD's avatar
ERD
Icon for Community Champion rankCommunity Champion
5 years ago

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's avatar
Anonymous
Not applicable
5 years ago

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's avatar
    ERD
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Thanks for the hint! Edited previous advise.