Power BI Matrix - Multiple Measures are Switch values to Rows and Month in Columns, need month wise

Hi sathish719 ,

To calculate month-wise differences for multiple measures displayed in rows within a Matrix visual, you need to create a DAX measure that dynamically calculates the difference between months for each measure. Since you have about 30 measures displayed on the row side, it’s best to avoid creating a separate difference measure for each one. Instead, a dynamic DAX measure will handle all measures at once.

First, ensure that your Matrix visual has measures on the row side, months on the column side, and values from your existing measures. To calculate the month-wise difference, create a DAX measure that identifies the current month and the previous month, and then subtracts the values accordingly.

The DAX formula to achieve this looks like this:

Month Difference =
VAR CurrentMonth = SELECTEDVALUE('Calendar'[Month])
VAR PrevMonth = CALCULATE(
    MAX('Calendar'[Month]),
    FILTER(ALL('Calendar'), 'Calendar'[Month] < CurrentMonth)
)
RETURN
IF(
    NOT ISBLANK(PrevMonth),
    CALCULATE([Total Value], 'Calendar'[Month] = CurrentMonth) -
    CALCULATE([Total Value], 'Calendar'[Month] = PrevMonth)
)

Replace [Total Value] with your base measure or use a SWITCH function if you are using a dynamic measure that aggregates various KPIs like Total Employee Count, Total Cost, etc.

If you are using a SWITCH-based dynamic measure, you can enhance the formula to calculate differences for each specific measure like this:

Month Difference = 
SWITCH(
    TRUE(),
    SELECTEDVALUE(Measures[Measure Name]) = "Total Emp Count", [Total Emp Count] - CALCULATE([Total Emp Count], PREVIOUSMONTH('Calendar'[Date])),
    SELECTEDVALUE(Measures[Measure Name]) = "Total Bill Emp", [Total Bill Emp] - CALCULATE([Total Bill Emp], PREVIOUSMONTH('Calendar'[Date])),
    SELECTEDVALUE(Measures[Measure Name]) = "Total Onsite Emp", [Total Onsite Emp] - CALCULATE([Total Onsite Emp], PREVIOUSMONTH('Calendar'[Date])),
    SELECTEDVALUE(Measures[Measure Name]) = "Total Offshore Emp", [Total Offshore Emp] - CALCULATE([Total Offshore Emp], PREVIOUSMONTH('Calendar'[Date])),
    SELECTEDVALUE(Measures[Measure Name]) = "Total Cost", [Total Cost] - CALCULATE([Total Cost], PREVIOUSMONTH('Calendar'[Date])),
    SELECTEDVALUE(Measures[Measure Name]) = "Total Benefits", [Total Benefits] - CALCULATE([Total Benefits], PREVIOUSMONTH('Calendar'[Date]))
)

Once this measure is created, add it to your Matrix visual to display the differences across months. The Matrix will then show the measures in rows, the months in columns, and the calculated differences for each measure dynamically.

For example, if your data includes measures such as Total Employee Count, Total Cost, and Total Benefits across January, February, and March, your Matrix will display the monthly values and the calculated differences like this:

In this Matrix, the calculated month differences will be dynamically applied to each measure without needing to create separate difference measures for each one. The dynamic DAX measure ensures that the differences are calculated correctly across all months and measures, saving you time and effort in maintaining your report.

Best regards,