Matrix showing wrong values

[aquamad96]

I am having a problem with matrix tables showing the correct values for my measures.

I have a table with Regions as rows, then I have my average $ per kg measures as values, including 3M and 12M averages.

However it seems these values will not populate the table correctly and just stick to the same average $ per kg value in column 2.

 

My measure for 3M Rolling is here:

3M Average Cumulative $ per kg =

VAR Three = CALCULATE([Average $ per kg],DATESINPERIOD('Air Freight Spend'[MonthYear], MAX('Air Freight Spend'[MonthYear]), -3, MONTH))

RETURN IF(MAX('Air Freight Spend'[MonthYear]) >= DATE(2022, 3, 1), Three, BLANK())

Is this something to do with my month year filter?

[Anonymous]

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.

[rajendraongole1 · Accepted solution]

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