Forum Discussion

sonya7's avatar
sonya7
Helper III
3 years ago
Solved

12 month rolling average

I have Amount data based on Startdate date. How can I write the 12-month average?

i'm new, it is important to me that you are clear 🙏 thanks

Need Help

16 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi sonya7 ,

    You can refer the following links to get it:

    Rolling 12 Months Average in DAX

    Sales R12M =
VAR NumOfMonths = 12
VAR LastCurrentDate =
    MAX ( 'Date'[Date] )
VAR Period =
    DATESINPERIOD ( 'Date'[Date], LastCurrentDate, - NumOfMonths, MONTH )
VAR Result =
    CALCULATE (
        AVERAGEX (
            VALUES ( 'Date'[Calendar Year Month] ),
            [Sales Amount]
        ),
        Period
    )
VAR FirstDateInPeriod = MINX ( Period, 'Date'[Date] )
VAR LastDateWithSales = MAX ( Sales[Order Date] )
RETURN
    IF ( FirstDateInPeriod <= LastDateWithSales, Result )
    • sonya7's avatar
      sonya7
      Helper III
      3 years ago

      Hi, to the extent that I use it for averaging, I unfortunately get incorrect results. Could you change the dimension in the image to its non-hierarchy?

       

       

    • sonya7's avatar
      sonya7
      Helper III
      3 years ago

      Hello, I want the following rolling average 4 result. But since this measure was created with quick measure, I could only use it with hierarchy.
      I want this measure to be used without a date hierarchy.