Forum Discussion
Dynamic column headers based on a slicer selection for Fiscal Years stored as text
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
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.
10 Replies
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.
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?
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.
Hi,
Thank you for your effort but I need to export this data so therefore need it in a matrix visual
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?Sort the headers by FY Order
