Dynamic Column Names and Measures in Power BI Matrix Based on Selected Year and Month

[Chanakya_vcr]

Question:

Hi Everyone,

I am trying to create a Power BI matrix visual to display sales measure for the Current Month Sales (CM), Previous Month Sales (CM-1), Last 2 Months Sales (CM-2), and Last 3 Months Sales (CM-3). I want the matrix to adjust both the column headers and measures dynamically based on the selected Year and Month filters.

Scenario:

I have the following structure:

Store Name CM-2 CM-1 CM

Store A	21819	1199	1414
Store B	7868	2137	1402
Store C	5895	1998	1690
Store D	3024	971	1196
Store E	3704	968	1058
Store F	2087	1271	1437

Expected Behavior:

1. When I filter by Year = 2024 and Month = Dec, the matrix should display:
   • CM as Dec-24
   • CM-1 as Nov-24
   • CM-2 as Oct-24
2. When I filter by Year = 2025 and Month = Jan, the matrix should display:
   • CM as Jan-25
   • CM-1 as Dec-24
   • CM-2 as Nov-24

Requirements:

• Dynamic Column Names: The column headers (CM, CM-1, CM-2) should update dynamically to reflect the selected Year and Month in the slicer.
• Dynamic Measures: The sales measures for CM, CM-1, CM-2, and CM-3 should align with the dynamically updated column names.
• Filters: The matrix should respond to both Year and Month filters, displaying the corresponding data accurately.

Question:

How can I implement this functionality in Power BI? Specifically:

-> Dynamically update column headers in the matrix table (e.g., Oct-24, Nov-24, Dec-24).

I would appreciate any guidance or suggestions on how to achieve this in Power BI.

Looking forward to your insights!

[Ashish_Mathur]

Hi,

I have solved a similar question in the attached file.

Hope this helps.

[Chanakya_vcr]

Hi @Ashish_Mathur ,

Thank you for sharing the PBIX file. While the solution with a new date table and userelationship works well, the issue arises when using the existing date table.

The existing date table Dim_Calendar has an active relationship with fact_Sales causing the Datesbetween logic to follow the default active filter context. In contrast, the new date table with an inactive relationship allows userelationship to explicitly control the filtering, which is why it works as expected.

Let me know if further clarification is needed!

[Ashish_Mathur]

You are welcome.  Not clear abut what you are trying to say.  What i have shown you in the file is the only way i am aware of solving the problem.

[Chanakya_vcr]

Thank you for your response. I have gone through your file, and the solution with a new date table (calendar_dup) and an inactive relationship worked perfectly. However, when I tried implementing the same logic using my existing date table (Dim_Calendar) with an active relationship, it didn’t work as expected.

That’s the point I was trying to highlight. Let me know if further clarification is needed!

[rohit1991]

Hi @Chanakya_vcr ,

You can achieve dynamic column headers and measures in a Power BI matrix based on selected Year and Month with this approach:

1. Create a proper Date Table Make sure your date table includes columns like Date, Year, Month, Month-Year, and any necessary sort columns. Mark it as your official date table.
   
   

2. Build Calculation Groups for Dynamic Headers Use Tabular Editor (external tool) to create a calculation group (let’s call it "Dynamic Columns") that will handle your headers: Create calculation items like CM, CM-1, CM-2. For each, write DAX expressions to dynamically set the header based on the selected period. For example: CM: SELECTEDVALUE('Date'[Month-Year])CM-1: FORMAT(EDATE(MIN('Date'[Date]), -1), "MMM-yy")CM-2: FORMAT(EDATE(MIN('Date'[Date]), -2), "MMM-yy"). This will update your column names dynamically as users select different months.
   
   

3. Write Measures for Each Period Create measures that align with these headers, using DAX to reference the selected Month-Year or the appropriate offset.
   
   

4. Matrix Setup Rows: Store Name Columns: Your calculation group (Dynamic Columns) Values: Your dynamic sales measures (CM, CM-1, etc.)

5. Add Slicers for Year and Month Use slicers on Year and Month from your date table to let users pick the period, and both headers and measures in the matrix will adjust accordingly.
   
   

If you have issues with context, make sure that you are using userelationship (for inactive relationships) or calculation groups with correct logic to avoid conflicts with active relationships on your main date table. This method gives you the flexible, dynamic behavior you want for both headers and measures, and works smoothly even as users switch Year and Month filters.

[Chanakya_vcr]

Hi @rohit1991,
Thank you for sharing this solution! I’ve already tried using a new date table, as suggested earlier, and it works perfectly in that scenario. However, the issue arises when I try to implement the same logic using the existing date table with an active relationship—it doesn’t work as expected.

I’m unsure if using Tabular Editor and calculation groups will resolve the issue in the context of the existing date table. I’ll explore this approach further and see if it makes a difference.

Appreciate your input, and I will follow up if I encounter any challenges during implementation.

[Ritaf1983]

Hi @Chanakya_vcr 
There are few workarounds to achieve the functionality of dynamic shown measures names.
Please relate to the linked videos:
https://www.youtube.com/watch?v=BLNqU2jJ7u0

https://www.youtube.com/watch?v=zbKNyFu2BOo

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