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 · Accepted solution]

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.

[asa70]

Hi @v-saisrao-msft , Apologies for the late reply. I have started using Power BI Report Builder for the solution.

I have a question on how to put the calculations on the same table. Through my exploration I have found 2 ways:

1. Creating a calculated table - How do I ensure that the Yearly Attrition calculation stills shows up and not just the Monthly Attrition calculation?
2. Creating two datasets and joining the tables - How would that be efficient and will it be relevant for the problem.

I'm still trying to figure out how to build the layout of the table, maybe that's where the solution will be?

Kind Regards,

Asanele 

[v-saisrao-msft]

Hi @asa70,

Since you’re now using Power BI Report Builder, you don’t need to create extra calculated tables or join multiple datasets. Just connect to your existing Power BI dataset and add both the Monthly Attrition and Yearly Attrition measures into the same tablix. Paginated reports are built for this type of formatted matrix layout, and using a single dataset keeps things simpler and more efficient.

Supported data sources for Power BI paginated reports - Power BI | Microsoft Learn

Paginated reports in Power BI: FAQ - Power BI | Microsoft Learn

Data retrieval guidance for paginated reports - Power BI | Microsoft Learn

Thank you.

[asa70]

Hi @v-saisrao-msft , thank you so much for the feedback. When I use both Yearly and Monthly Attrition calculation, how would I add them in the same dataset?

[v-saisrao-msft]

Hi @asa70,

Please find the below attached pbix file for reference:

Thank you.

[asa70]

Hi @v-saisrao-msft , thank you so much for the feedback. I can see how this would work in Power BI. My question was in reference to Report Builder. How would I ensure that:

1. The Dataset includes both Attrition calculations for Year and Month because there are 2 separate calculations depending on the Year or Month shown in the visual.

2. Showing on the columns the Years and Current Year, with the last 3 months.

I hope that helps

Kind Regards

[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" )