Matrix Visual: Combing Two Calculations and Fields Into Visual

[asa70]

Hi all, 

I hope you're all doing well.

I have a task where I need to showcase two calculations on a Matrix Visual:

1. A Yearly Calculation for the ones in Green 
2. A 12 Month Trailing Calculation for the ones in Orange. 

The DAX for the 2 calculations have been created and work fine individually. It's when I have to combine the 2 measures into visual in the screenshot.

These are the rules that were provided:

1. For the current or max year, only show the 12 Month Trailing Calculation for current/max month, current/max month - 1 and current/max month - 2
2. For the previous years, only showing YTD 

Any tips or advice on how to show the 2 calculations to get the required view in the screenshot. It would be really appreciated!

I have attached the sample data for the screenshot.

Kind Regards,
Asa

Month	Year	Attrition
Jan	2023	10
Feb	2023	2
Mar	2023	5
Apr	2023	8
May	2023	9
Jun	2023	2
Jul	2023	6
Aug	2023	0
Sept	2023	7
Oct	2023	7
Nov	2023	10
Dec	2023	6
Jan	2024	9
Feb	2024	9
Mar	2024	0
Apr	2024	1
May	2024	2
Jun	2024	5
Jul	2024	8
Aug	2024	5
Sept	2024	4
Oct	2024	10
Nov	2024	2
Dec	2024	1
Jan	2025	3
Feb	2025	2
Mar	2025	2
Apr	2025	0
May	2025	2
Jun	2025	0
Jul	2025	9
Aug	2025	5

[v-saisrao-msft]

Hi @asa70,

Have you had a chance to review the solution we shared earlier? If the issue persists, feel free to reply so we can help further

Thank you.

[asa70]

Hi @v-saisrao-msft, I'm looking into the Paginated Report solution and learning about it first. I will revert back once I have designed the view. Thank you!

[v-saisrao-msft]

Hi @asa70,

Checking in to see if your issue has been resolved. let us know if you still need any assistance.

Thank you.

[v-saisrao-msft]

Hi @johnt75,

Just checking in to see if you were able to design the view, and if the issue has been resolved.

Thank you.

[v-saisrao-msft]

Hi @asa70,

Thank you for the update.

[v-saisrao-msft]

Hi @asa70,

Thank you @audreygerred,@johnt75, for your insights.

If your primary requirement is to replicate the appearance and formatting of the table with grouped headers and custom spans as shown in your screenshot, then using a Paginated Report would be the more suitable choice for your scenario.

Thank you.

[audreygerred]

Hi! You will likely be able to achieve this using pagination/Report Builder. It has less rigid formating than a matrix in Power BI due to its pixel perfect nature.

[johnt75]

I think the easiest way would be to create a calculation group with calculation items for each column, something like

Year - 2 =
VAR LastDateWithValues =
    CALCULATE ( MAX ( Sales[Date] ), REMOVEFILTERS () )
VAR Result =
    CALCULATE (
        SELECTEDMEASURE (),
        'Date'[Year]
            = YEAR ( LastDateWithValues - 2 ),
        REMOVEFILTERS ( 'Date' )
    )
RETURN
    Result

and 

Month - 2 =
VAR LastDateWithValues =
    CALCULATE ( MAX ( Sales[Date] ), REMOVEFILTERS () )
VAR RefDate =
    EOMONTH ( LastDateWithValues, -2 )
VAR DatesToUse =
    DATESINPERIOD ( 'Date'[Date], RefDate, -12, MONTH )
VAR Result =
    CALCULATE ( SELECTEDMEASURE (), DatesToUse )
RETURN
    Result

The key is to take the MAX of your fact table date column, not of 'Date'[Date].

You could also create a measure for the month name labels like

Label =
FORMAT ( MAX ( 'Date'[Date] ), "mmmm" )