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

[VannurVali]

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!

Thank you!

Best Regards,

Vannur Vali

[Anonymous · Accepted solution]

Hi @VannurVali ,

I think you can add an unrelated year and month selection table to help calculation.

My Sample is as below.

Calendar = 
ADDCOLUMNS (
    CALENDARAUTO (),
    "Year", YEAR ( [Date] ),
    "Month", FORMAT ( [Date], "MMM" ),
    "MonthSort", MONTH ( [Date] ),
    "YearMonth", FORMAT ( [Date], "MMM-YY" ),
    "YearMonthSort",
        YEAR ( [Date] ) * 100
            + MONTH ( [Date] )
)

Selection = SUMMARIZE('Calendar','Calendar'[Year],'Calendar'[Month],'Calendar'[MonthSort])

Measure:

Measure = 
VAR _SELECTYEAR = SELECTEDVALUE(Selection[Year])
VAR _SELECTMONTH = SELECTEDVALUE(Selection[MonthSort])
VAR _RANGEEND = EOMONTH(DATE(_SELECTYEAR,_SELECTMONTH,1),0)
VAR _RANGESTART = EOMONTH(_RANGEEND,-3)+1
RETURN
CALCULATE(SUM('Table'[Sales]),FILTER('Calendar','Calendar'[Date]>=_RANGESTART && 'Calendar'[Date]<=_RANGEEND))

Result is as below.

Year = 2025 Month = Jan

Year = 2024 Month = Dec

Best Regards,
Rico Zhou

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

[johnt75]

I think you would need to lay card visuals over the top of the column headings, and have measures in the card visuals which give the appropriate month name.

For the columns themselves, this is a perfect application of a calculation group. Create calculation items for the 4 months like

Current month = SELECTEDVALUE()

Current month -1 = CALCULATE( SELECTEDMEASURE(), DATEADD( 'Date'[Date], -1, MONTH) )

You could get the month names with something like

Current month name =
VAR _SelectedValue = SELECTCOLUMNS(
    SUMMARIZE(
        'Date',
        'Date'[Month in Calendar],
        'Date'[Month Year]
    ),
    'Date'[Month in Calendar]
)
RETURN IF( COUNTROWS( _SelectedValue ) = 1, _SelectedValue )

Month - 1 Name = CALCULATE( [Current month name], DATEADD( 'Date'[Date], -1, MONTH) )