Dynamic matrix headers for rolling 12 months

[pvb]

Hi all,

I'm currently working on a project to replace a legacy reporting solution with Power BI. One of the reports contains a matrix
with the stock ticker on the rows and various KPIs on the columns including a rolling 12 months based on the user's date selection with the month name in the table header.

E.g: User selected month August:

Ticker	YTD	MTD	September	October	November	December	January	February	March	April	May	June	July	August
MSFT	12%	0.4%	2%	1.5%	-0.3%	-0.1%	0%	1.2%	0.2%	1.4%	-1.3%	1.7%	2.5%	-1.2%

Is there any way to make this happen? I've tried to remove the header and calculate the month names in DAX measure and put it in the first row which would generally work, but the sort functionality is lost. Also placing card visuals or any other visual on top of the headers doesn't work since the table needs to be scrollable horizontally.

Thanks

[pvb]

thanks, indeed this pointed me to the right direction and the output is now as expected. Thanks a lot

[Anonymous · Accepted solution]

Hi @pvb

You can try the following steps:

1. Create a calculated table as follows. And create a slicer with this table.

2. Create a measure as follows:

value = 
VAR _RANGESTART =
    EOMONTH ( MAX ( 'Date'[Date] ), -13 ) + 1
VAR _RANGEEND =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Table'[KPI] ),
        FILTER ( 'Table', 'Table'[Date] >= _RANGESTART && 'Table'[Date] <= _RANGEEND )
    )

3. Select a matrix visualization as shown here.

Is this the result you expect?

Best Regards,
Community Support Team _Yuliax

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.