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

average
Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

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

  • 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.