Forum Discussion

ggargan's avatar
ggargan
Frequent Visitor
3 years ago
Solved

Problems calculating 12 month rolling average

Hello. Havent done this before.   Trying to calculate the 12 month rolling average for a rate (Measure). It doesn't seem to be working and is just returning the same value as the current month's va...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi ggargan ,

    Please try below steps:

    1. below is my test table

    Table:

    2. create a measure with below dax formula

    Measure =
VAR cur_date =
    SELECTEDVALUE ( 'Table'[Date] )
VAR tmp =
    CALCULATETABLE (
        VALUES ( 'Table'[Sales] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] IN DATESINPERIOD ( 'Table'[Date], cur_date, -12, MONTH )
        )
    )
RETURN
    AVERAGEX ( tmp, [Sales] )

    3. add a table visual with measure and column

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_ Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
3 years ago

Hi,

Create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name by the Month number.  Create a relationship (Many to One and Single) from the Date column of the Data Table to the Date column of the Calendar Table.  To your Table visual, drag Year and Month name from the Calendar Table.  Assuming 5. TRIR is a measure that you hav already written ,write this measure:

 12 months rolling average = calculate([5. TRIR],datesbetween(Calendar[Date],edate(min(calendar[date)),-11),max(calendar[date])))

Hope this helps.