Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I would like to calculate the trailing 6 month average for Margin % (A), Margin % (B), Margin % (C). They are measures instead of columns.
I tried several ways but none of them worked for me. I am wondering if I can find some luck here. Thank you in advance.
Hi @PB_MZ ,
I think you can refer to below code to create measures.
Trailing 6 months average for Margin % (A) =
VAR _EndDate =
MAX ( DimDate[Date] )
VAR _StartDate =
EOMONTH ( _EndDate, -7 ) + 1
RETURN
AVERAGEX (
FILTER (
ALL ( DimDate ),
DimDate[Date] >= _StartDate
&& DimDate[Date] <= _EndDate
),
[Margin % (A)]
)
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This is the results I would like to get.
Hi,
Thank you for your response. But unforturnately, I didn't get the results I want by your way. Correct me if I was wrong.
This is the code:
And this is the result from your code:
Hi,
Sorry for the late resposne. Trailing 6 months average means I need to calculate the average of (previous 5 months + current month) for each month.
User | Count |
---|---|
40 | |
26 | |
22 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |