Dynamic column headers based on a slicer selection for Fiscal Years stored as text

[mp390988]

Hi,

I have two measures, Total Revenue and Previous Period Revenue.
I have the below visuals: a slicer that contains the FY column from my date table and then I have a matrix visual containing the 2 measures.

What I want to achieve is to display the column headers for the matrix visual depending on the slicer selection.
So for example, based on the slicer selection in the snapshot above, Total Revenue should be replaced with "Revenue for 2024-2025" and Previous Period Revenue should be replaced with "Revenue for 2023-2024".

The FY in my dim date table is a calculated column and is stored as TEXT values as shown below.

Does anyone know how to do this please?

You can download the pbi file here 

Thank You

[mp390988]

Hi @danextian ,

How do you order the headers so that 2025-2026 comes first, then 2024-2025, then 2023-2024 and so forth.

Right now, they are ordered opposite to what I want.

Thank You

[danextian]

Sort the headers by FY Order

[danextian · Accepted solution]

Hi @mp390988 

Add a column that will make it easy to identify the previous FY based on the current selection.

Create a disconnected table with referencing the original dates table and with additional column for the headers.

Create this measure

Dynamic Measure = 
VAR _CurrOrder =
    MIN ( 'Dim Date'[FY Order] )
VAR _ValidOrders =
    { _CurrOrder, _CurrOrder + 1 }
VAR _DynamicHeaderOrder =
    MIN ( DynamicHeaders[FY Order] )
RETURN
    CALCULATE (
        [Total Revenue],
        REMOVEFILTERS ( 'Dim Date'[FY], 'Dim Date'[FY Order] ),
        TREATAS (
            INTERSECT (
                _ValidOrders,
                { _DynamicHeaderOrder }
            ),
            'Dim Date'[FY Order]
        )
    )

Please see the attached PBIX.

[mp390988]

Hi @danextian ,

Thank you so much for your solution, I am very grateful.

I just have a few questions around your solution as follows:

1).  I am not sure what use the variable _DynamicHeaderOrder has? It will always evaluate to 1, unless I am mistaken.  In the Intersect function, I see you have used this variable in there but say for example, _CurrOrder = 3 then _ValidOrders =  {3,4} and we know _DynamicHeaderOrder = 1 then the Intersect of _ValidOrders {3,4} and _DynamicHeaderOrder {1} will return empty, right?

[danextian]

FY order is simply the ranking of FY with the latest to be always 1. The min rank changes with the slicer selection. Add 1 to that and you get the previous FY.  The attached pbix shows it all. Please review that.

[mp390988]

Hi @danextian ,

Thank you very much for your solution.

I have reviewed the pbix file and stepped through the measure carefully to understand how it behaves and now I understand.

Thank You

[mp390988]

I managed to do this by creating a field paramter as follows:

Parameter =

{

    ("Revenue for 2024-2025", NAMEOF('My Measures'[Total Revenue]), 0, "2024-2025"),

    ("Previous Period Revenue 2023-2024", NAMEOF('My Measures'[Previous Period Revenue]),1, "2024-2025"),

    ("Revenue for 2023-2024", NAMEOF('My Measures'[Total Revenue]), 0, "2023-2024"),

    ("Previous Period Revenue 2022-2023", NAMEOF('My Measures'[Previous Period Revenue]),1, "2023-2024"),

    ("Revenue for 2022-2023", NAMEOF('My Measures'[Total Revenue]), 0, "2022-2023"),

    ("Previous Period Revenue 2021-2022", NAMEOF('My Measures'[Previous Period Revenue]),1, "2022-2023"),

    ("Revenue for 2021-2022", NAMEOF('My Measures'[Total Revenue]), 0, "2021-2022"),

    ("Previous Period Revenue 2020-2021", NAMEOF('My Measures'[Previous Period Revenue]),1, "2021-2022"),

    ("Revenue for 2020-2021", NAMEOF('My Measures'[Total Revenue]), 0, "2020-2021"),

    ("Previous Period Revenue 2019-2020", NAMEOF('My Measures'[Previous Period Revenue]),1, "2020-2021")

}

but this means a lot of hardcoding of values and requires constant update when new financial years get added.

How can I make this dynamic?

[PijushRoy]

Hi @mp390988 
You can also build this in the new card visual. Please find the step in Snap and the attached PBIX file instead of 
Matrix table column header which is not avaialble

[mp390988]

Hi,

Thank you for your effort but I need to export this data so therefore need it in a matrix visual

[PijushRoy]

Hi @mp390988 
This is possible in a card visual. Please create a measure and use the measure in the Card title.
Please find the attached solution and PBIX file

Please keep me posted if that works