Forum Discussion

VannurVali's avatar
VannurVali
Icon for Helper I rankHelper I
1 year ago
Solved

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

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

2181911991414

Store B

786821371402

Store C

589519981690

Store D

30249711196

Store E

37049681058

Store F

208712711437

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

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

2 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    1 year ago

    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) )
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.