Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 value (not the average of the last 12 months).
This is the DAX in the calculation
Would be very greatful if someone could point out where im going wrong! Let me know if you need any more info.
Solved! Go to Solution.
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.
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.
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.
Hi, @ggargan
Did you try quick measure for that, it might work?
Proud to be a Super User!
Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |