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

[lovekheart]

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!

[v-pgoloju]

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

[OwenAuger]

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.

[lovekheart]

@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

[OwenAuger · Accepted solution]

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?

[lovekheart]

This works perfect thank you for all you help!