Dynamically limit output columns in matrix

Hi LesleyR , Thank you for reaching out to the Microsoft Community Forum.

Assuming your dimCalendar has [Date] and [WeekOffset] columns, and factData is linked to it, I suggest creating a calculated table to lock in exactly 99 periods based on the selected period type. This keeps your matrix columns at 99, adapting dynamically to your date range filter. It ranks days or weeks efficiently and uses the full calendar for months.
Last99Periods =

VAR _period = SELECTEDVALUE(par_period[par_period Order])

VAR _maxDate = CALCULATE(MAX(dimCalendar[Date]), ALLSELECTED(dimCalendar))

VAR _maxWeek = CALCULATE(MAX(dimCalendar[WeekOffset]), ALLSELECTED(dimCalendar))

RETURN

SWITCH(

    TRUE(),

    _period = 0,

        SELECTCOLUMNS(

            FILTER(

                ADDCOLUMNS(

                    CALENDAR(_maxDate - 98, _maxDate),

                    "Rank", RANKX(CALENDAR(_maxDate - 98, _maxDate), [Date], , ASC)

                ),

                [Rank] <= 99

            ),

            "Period", [Date],

            "Rank", [Rank]

        ),

    _period = 1,

        SELECTCOLUMNS(

            FILTER(

                ADDCOLUMNS(

                    FILTER(dimCalendar, [WeekOffset] >= _maxWeek - 98 && [WeekOffset] <= _maxWeek),

                    "Rank", RANKX(FILTER(dimCalendar, [WeekOffset] <= _maxWeek), [WeekOffset], , ASC)

                ),

                [Rank] <= 99

            ),

            "Period", CALCULATE(MIN(dimCalendar[Date]), FILTER(dimCalendar, [WeekOffset] = EARLIER([WeekOffset]))),

            "Rank", [Rank]

        ),

    _period = 2, dimCalendar

)

For the values, I recommend a measure that handles your metric selection (count or percent) and applies the right filter for each period. It also uses the matrix’s grand total for the 100th column, formatting percentages as needed, and it’s optimized to avoid unnecessary calculations.

DynamicMetric =

VAR _period = SELECTEDVALUE(par_period[par_period Order])

VAR _metric = SELECTEDVALUE(par_metric[par_metric Order])

VAR _selectedPeriod = SELECTEDVALUE(Last99Periods[Period])

VAR _total = CALCULATE(

    SWITCH(_metric, 0, [Metric 1], 1, IF(_period < 1, [Metric 2], [Metric 3])),

    ALLSELECTED(dimCalendar[Date])

)

RETURN

IF(

    ISBLANK(_selectedPeriod),

    IF(_metric = 1, FORMAT(_total, "0.0%"), _total),

    VAR _result = CALCULATE(

        SWITCH(_metric, 0, [Metric 1], 1, IF(_period < 1, [Metric 2], [Metric 3])),

        dimCalendar[Date] = _selectedPeriod

    )

    RETURN IF(_metric = 1, FORMAT(_result, "0.0%"), _result)

)

To make it work, just set up your matrix with the table’s period column and the measure, then turn on grand totals. This gives you 99 periods plus a clean total column, all tied to your dynamic filters without extra visuals or hacks.

If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.