Showing month columns(Up coming months) in a matrix based on month selected from the slicer

[WinterGarden]

Hi All,

I wanted to create a matrix, in which based on the month value selected in the slicer, i wanted to show 10 month columns on/after the selcted month.

this is how my current matix visual looks like:

Slicer:
  Upcoming Date[MonthYear] (this is an independent table.. no relationship is created):

Upcoming Date =

ADDCOLUMNS (

    CALENDAR (DATE(2025,1,1), DATE(2035,12,31)),  -- adjust range

    "MonthYear", FORMAT([Date], "mmm-yy"),

    "FY",FORMAT([Date], "yyyy")

)

Matrix:

Row and values from : "All Items" table
Column from : CalendarTable[MonthYear]

CalendarTable =

ADDCOLUMNS (

    CALENDAR (DATE(2025,1,1), DATE(2035,12,31)),  -- adjust range

    "MonthYear", FORMAT([Date], "mmm yyyy"),

    "YearMonthNum", YEAR([Date]) * 12 + MONTH([Date]) - 1,

    "Year", YEAR([Date])

)

There is One to many relationship from CalendarTable[Date] to All Items[Delivery Date]

Task:
if i select Jan-25 in the silcer, then i wanted to show only 10 month columns on or after jan-25 in the matrix

now all the month columns are showing in the matrix:

but i want to show only 10 month columns from jan 2025.. ie jan 2025, feb 2025,.....oct 2025
if Feb 2025 is selected then columns from Feb 2025 to nov 2025.

Fo that i have created a measure to filter that matrix. but it is not working:

ShowInMatrix =

VAR SelectedMonth =

    MINX(

        FILTER(

            ALL('Upcoming Date'),

            'Upcoming Date'[MonthYear] = SELECTEDVALUE('Upcoming Date'[MonthYear])

        ),

        'Upcoming Date'[Date]

    )

VAR CurrentMonth = MIN(CalendarTable[Date])

RETURN

IF (

    NOT ISBLANK(SelectedMonth) &&

    CurrentMonth >= SelectedMonth &&

    CurrentMonth < EDATE(SelectedMonth, 10),

    1,

    0

)

how can i filter columns in matrix? Could you please help?

[danextian · Accepted solution]

Hi @WinterGarden 

Use a disconnected dates table  (without relationship to your fact or dates table) as using related one or from the same table as fact will only show rows based on slicer selection. Then create this measure:

Disconnected Total Revenue = 
VAR _SelectedPeriod =
    MIN ( DisconnectedDate[Date] )
VAR _P10 =
    EOMONTH ( _SelectedPeriod, 9 )
RETURN
    CALCULATE (
        [Total Revenue],
        KEEPFILTERS ( Dates[Date] >= _SelectedPeriod && Dates[Date] <= _P10 )
    )

Please see the attached pbix.

[WinterGarden]

@danextian ,

Thank you so much!! This is working😊

[Shahid12523]

To show only 10 month columns after the selected slicer month in your matrix:
- Create a calculated column in CalendarTable:

ShowMonthFlag =
VAR SelectedMonth =
CALCULATE (
MINX (
FILTER (
ALL('Upcoming Date'),
'Upcoming Date'[MonthYear] = SELECTEDVALUE('Upcoming Date'[MonthYear])
),
'Upcoming Date'[Date]
)
)
RETURN
IF (
CalendarTable[Date] >= SelectedMonth &&
CalendarTable[Date] < EDATE(SelectedMonth, 10),
1,
0
)

- Add ShowMonthFlag to your matrix visual-level filters and set it to 1.
That’ll restrict columns to just the 10 months starting from the selected slicer value.

[danextian]

You're seriously telling OP to create a calculated column?  Calculated columns are not aware of slicer selections. They are computed once when data is loaded, refreshed or the formula is modified. Use AI responsibily. Validate and not just copy-paste whatever it spits.

[Aburar_123]

Hi @WinterGarden ,

Please follow the below steps.

My data model:

Dim Date1 = ADDCOLUMNS(CALENDARAUTO(12),

"Year Month",YEAR([Date])&"-"&FORMAT(MONTH([Date]),"0#"),

"Year Month Sort",YEAR([Date])&FORMAT(MONTH([Date]),"0#")

)

Dim Date2 = ADDCOLUMNS(CALENDARAUTO(12),

"Year Month",YEAR([Date])&"-"&FORMAT(MONTH([Date]),"0#"),

"Year Month Sort",YEAR([Date])&FORMAT(MONTH([Date]),"0#")

)

Note : Make sure 'Year Month Sort' column should be whole Number format and Sort the 'Year Month' column by 'Year Month Sort' column.

Just create the below measure that you are going to use it in the values card in the Matrix visual,

Sales_Last_10Months = CALCULATE(SUM(FACT_Orders[Sales]),FILTER('Dim Date1','Dim Date1'[Year Month Sort]>=MAX('Dim Date2'[Year Month Sort]) && 'Dim Date1'[Year Month Sort]< MAX('Dim Date2'[Year Month Sort])+10))

In this case you dont need to create any flag column to filter in the visual filter page.

Thanks.

[WinterGarden]

Hi @Aburar_123 ,

Tried to implement this, but it is not showing next year data..
eg if 2025-04 is selected , then it should show columns from 2025-04 to 2026-01 right?

[Ashish_Mathur]

Hi,

I have solved a similar question in the attached file.

Hope this helps.

[Jihwan_Kim]

Hi,

One of ways is to create disconnected slicer table and calculation group to filter the visualization.

I tried to create a sample pbix file, and please check the below picture and the attached pbix file.

[WinterGarden]

Hi @Jihwan_Kim ,
Thank you, but the ask is to show the month year in columns.. 
in the rows... we are displaying project, category and in the values we are using a value column..
based on the month year selected , i wanted to show 10 months on or after that selected month in the columns of that matrix.

[koikoi]

thanks

[FBergamaschi]

Hi @WinterGarden 

you need to use the YearMonthNr column that you already have (why the -1 in its definition?) and include it into your disconnected table, that will simplify the issue

Upcoming Date =

ADDCOLUMNS (

    CALENDAR (DATE(2025,1,1), DATE(2035,12,31)),  -- adjust range

    "MonthYear", FORMAT([Date], "mmm-yy"),

    "FY",FORMAT([Date], "yyyy"),
     "YearMonthNum", YEAR([Date]) * 12 + MONTH([Date]) - 1

)

ShowInMatrix =

VAR SelectedYearMonthNr  = SELECTEDVALUE('Upcoming Date'[YearMonthNum])

VAR MaxYearMonthNr = SelectedYearMonthNr + 10
Var CurrentYearMonthNrInMatrix = SELECTEDVALUE ( 'Calendar Table'[YearMonthNum] )

RETURN

IF (

    NOT ISBLANK(SelectedYearMonthNr) &&

    CurrentYearMonthNrInMatrix >= SelectedYearMonthNr &&

    CurrentYearMonthNrInMatrix < MaxYearMonth,

    1,

    0

)

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[WinterGarden]

Hi @FBergamaschi ,

I've created the specified column and updated the measure as well.. then i add the measure "ShowInMatrix" in the filter pane of that matrix visual..

still the months columns are not getting filtered. Also the row values are now coming as blank. PLease find the below screenshot.

[FBergamaschi]

Hello @WinterGarden,

weird as I tried and it works

Can you check the file I put here and confirm ir works or not?

https://drive.google.com/drive/folders/1NTqfUR6s5mNslyr9QpI-tif9I-Vp4Y3S?usp=sharing

This is my result

With this approach you can change the measure at your will in the visual. Another approach I like is a calculation group, in case.

Thanks