Forum Discussion
Matrix showing wrong values
Hi aquamad96 -you can use SUMMARIZE and CALCULATETABLE, to gain more control over the rolling period, ensuring that you're averaging the last 3 months correctly.
modified measure:
3M Average Cumulative $ per kg =
VAR LastVisibleDate = MAX('Air Freight Spend'[MonthYear])
VAR ThreeMonthPeriod =
CALCULATETABLE(
SUMMARIZE(
'Air Freight Spend',
'Air Freight Spend'[MonthYear],
"AvgPerKg", [Average $ per kg]
),
DATESINPERIOD(
'Air Freight Spend'[MonthYear],
LastVisibleDate,
-3,
MONTH
)
)
VAR ThreeMonthAvg =
AVERAGEX(ThreeMonthPeriod, [AvgPerKg])RETURN
IF(
LastVisibleDate >= DATE(2022, 3, 1),
ThreeMonthAvg,
BLANK()
)Hope it helps, check the above measure let know
Hi aquamad96 ,
Based on the information you provided, I have created the following data table.
Your 3M Rolling measure works fine for me.
Can you provide some sample data? We can better understand the problem and help you.
Or show it as a screenshot or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.
Please see the attached pbix for refrence.
Best Regards,
Dengliang Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.