topic Re: Create a Matrix that has 3,6,12 month averages of measures in DAX Commands and Tips

Create a Matrix that has 3,6,12 month averages of measures

lovekheart — Wed, 25 Jun 2025 19:00:29 GMT

This picture showcases the end goal (sorry i dont have a better example) that i am trying to acheive. I do have a calender table and all the measures but cant seem to find how to achieve this for the matrix visual. Please reach out with any knowldege on this!

Re: Create a Matrix that has 3,6,12 month averages of measures

OwenAuger — Thu, 26 Jun 2025 00:48:55 GMT

Hi lovekheart

I would recommend a calculation group containing calculation items for each of the average calculations.

https://learn.microsoft.com/en-us/power-bi/transform-model/calculation-groups

Each calculation item's expression should contain SELECTEDMEASURE ( ) as a placholder for the measure it will be applied to.

For example, if your averages are defined as arithmetic means of monthly measure values for the last N months (as at the max date filtered), you might use this calculation item expression for "Last 3 months":

CALCULATE ( AVERAGEX ( VALUES ( 'Date'[Year Month] ), -- or column with month/year granularity SELECTEDMEASURE ( ) ), DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -3, MONTH ) )

You could then construct the matrix with:

Columns: Calculation group column
Values: All required measures
"Show values in rows" enabled

Is that enough to go on?

I could create a small example to demonstrate the setup if that would be useful.

Re: Create a Matrix that has 3,6,12 month averages of measures

lovekheart — Thu, 26 Jun 2025 13:27:30 GMT

OwenAugerHi thank you for this. I am still a bit confused (maybe sample will help more). I created the calculation group and by itself on the matrix it pops up correctly before adding values. I changed the values in rows, but my chart is just blank and changes to show "calculation group column" instead of "Last 3 Months"...
I'm guessing my measures arent created properly for example all of them are in this similar example
measure = divide([measure 5],[measure 2],0) * 100

Re: Create a Matrix that has 3,6,12 month averages of measures

Anonymous — Fri, 27 Jun 2025 03:37:35 GMT

Hi lovekheart,

Thank you for reaching out to the Microsoft Fabric Forum Community.

Try modifying the dax measure below

Measure = AVERAGEX (VALUES ( 'Date'[YearMonth] ),DIVIDE ( [Measure 5], [Measure 2], 0 ) * 100)

Best regards,
Prasanna Kumar

Re: Create a Matrix that has 3,6,12 month averages of measures

OwenAuger — Fri, 27 Jun 2025 03:51:00 GMT

No worries, here's a sample PBIX.

The exact definitions of your measures shouldn't matter as long as they are explicit measures (which it sounds like they are) and respond correctly to 'Date' filters.

I've created two calculation groups in my sample PBIX:

Monthly Averages relative to Filtered Date
Computes monthly averages relative to the max date filtered.
Monthly Averages relative to Current Month
Computes monthly averages relative to the "current date" as returned by the TODAY ( ) function.

The calculation item expressions differ only in how they define the "reference date". I've also added variables for readability.

Sample calculation items:

------------------------------------------------------------------ -- Calculation Group: 'Monthly Averages relative to Current Month' ------------------------------------------------------------------ Last 12 Months = VAR ReferenceDate = EOMONTH ( TODAY ( ), 0 ) VAR NumMonths = 12 RETURN CALCULATE ( AVERAGEX ( VALUES ( 'Date'[Year Month] ), SELECTEDMEASURE ( ) ), DATESINPERIOD ( 'Date'[Date], ReferenceDate, -NumMonths, MONTH ) )

------------------------------------------------------------------ -- Calculation Group: 'Monthly Averages relative to Filtered Date' ------------------------------------------------------------------ Last 12 Months = VAR ReferenceDate = MAX ( 'Date'[Date] ) VAR NumMonths = 12 RETURN CALCULATE ( AVERAGEX ( VALUES ( 'Date'[Year Month] ), SELECTEDMEASURE ( ) ), DATESINPERIOD ( 'Date'[Date], ReferenceDate, -NumMonths, MONTH ) )

Are you able to get something similar working?

Re: Create a Matrix that has 3,6,12 month averages of measures

lovekheart — Fri, 27 Jun 2025 13:29:58 GMT

This works perfect thank you for all you help!