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.
Hi
So I can't get below DAX to work properly. It calculates the average fine on the the year basis.
However I can't get it to calculated moving 12M average on a monthly basis (see picture below).
I hope someone way smarter than me can figure this out 🙂
Unit_R12M =
VAR NumOfMonths = 12
VAR LastSelectedDate = MAX('data newest'[Date])
VAR Period =
DATESINPERIOD('data newest'[Date], LastSelectedDate, -NumOfMonths, MONTH)
VAR Result =
CALCULATE(
AVERAGEx(
DISTINCT(VALUES('data newest'[Date])),
[Unit_SUM]
),
Period
)
Return
Result
Solved! Go to Solution.
Hi,
Please check the below picture and the attached pbix file.
I tried to create a sample pbix file like below, and I hope the below can provide some ideas on how to create a solution for your datamodel.
Rolling 12M avg: =
VAR NumOfMonths = 12
VAR LastSelectedDate =
MAX ( 'Calendar'[Date] )
VAR Period =
DATESINPERIOD ( 'Calendar'[Date], LastSelectedDate, - NumOfMonths, MONTH )
VAR Result =
CALCULATE (
AVERAGEX ( DISTINCT ( VALUES ( 'Calendar'[Month Name] ) ), [Unit sum:] ),
Period
)
RETURN
Result
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
Please check the below picture and the attached pbix file.
I tried to create a sample pbix file like below, and I hope the below can provide some ideas on how to create a solution for your datamodel.
Rolling 12M avg: =
VAR NumOfMonths = 12
VAR LastSelectedDate =
MAX ( 'Calendar'[Date] )
VAR Period =
DATESINPERIOD ( 'Calendar'[Date], LastSelectedDate, - NumOfMonths, MONTH )
VAR Result =
CALCULATE (
AVERAGEX ( DISTINCT ( VALUES ( 'Calendar'[Month Name] ) ), [Unit sum:] ),
Period
)
RETURN
Result
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi Jihwan
Okay, I can now see where the problem lies. I didn't have a seperate calendar table in my datamodel. I was just using dates in the extracted data.
I've now build a Calendar table in my datamodel, and used those dimensions in my DAX calculation for my R12 average, and now I'm getting the right result.
Thanks a lot.
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |