Forum Discussion

pvb's avatar
pvb
Frequent Visitor
2 years ago
Solved

Dynamic matrix headers for rolling 12 months

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:

 

TickerYTDMTDSeptemberOctoberNovemberDecemberJanuaryFebruaryMarchAprilMayJuneJulyAugust
MSFT12%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

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

2 Replies

  • pvb's avatar
    pvb
    Frequent Visitor
    2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.