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 Sale...
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.

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) )