Matrix with dynamic column value.

Hi GFire ,

 

To achieve a dynamic date range that includes the prior month, you can create a DAX measure and apply it as a filter to your matrix visual. This approach will make the matrix automatically show both the selected month and the entire previous month based on your slicer selections.

 

First, you'll need to create a new measure in your Power BI model. In the formula bar, you should enter the following DAX expression. It's important to replace 'Date'[Year], 'Date'[MonthNumberOfYear], and 'Date'[Date] with the actual column names from your own date table.

Show Date Range = 
VAR SelectedYear = SELECTEDVALUE('Date'[Year])
VAR SelectedMonth = SELECTEDVALUE('Date'[MonthNumberOfYear])
VAR StartDate = DATE(SelectedYear, SelectedMonth - 1, 1)
VAR EndDate = EOMONTH(DATE(SelectedYear, SelectedMonth, 1), 0)
VAR CurrentDateInMatrix = MAX('Date'[Date])
RETURN
IF(
    CurrentDateInMatrix >= StartDate && CurrentDateInMatrix <= EndDate,
    1,
    BLANK()
)

After you have created this measure, select your matrix visual on the report canvas. Drag the new [Show Date Range] measure into the "Filters on this visual" section of the Filters pane. Set the filter condition to "is not blank" and click "Apply filter". Once this is done, your matrix will dynamically update. For example, with May 2025 selected in your filters, the matrix columns will now correctly display all days from both April 2025 and May 2025.

 

Best regards,

Hi GFire ,
Thank you for reaching out to Microsoft fabric community.

Try these steps to resolve your issue -
Import or Create a Date Table

DateTable = 
ADDCOLUMNS (
    CALENDAR (DATE(2024, 1, 1), DATE(2026, 12, 31)),
    "Year", YEAR([Date]),
    "Month", MONTH([Date]),
    "MonthName", FORMAT([Date], "MMMM"),
    "YearMonth", FORMAT([Date], "YYYY-MM")
)

Next, mark this table as a date table:

Right-click the table, choose Mark as Date Table, and select the Date column.

Create Month-Year Selector Table

MonthYearSelector = 
ADDCOLUMNS (
    GENERATE (
        VALUES ( DateTable[Year] ),
        VALUES ( DateTable[Month] )
    ),
    "MonthName", FORMAT(DATE([Year], [Month], 1), "MMMM")
)

This table can be used to create dropdown slicers for Year and Month.

Import or Enter Your Fact Table
Name this table example - WorkLog.

Create Relationship

Connect WorkLog[Date] to DateTable[Date]

Add Dynamic Filter Column in Date Table
ShowInMatrix = 
VAR SelectedYear = SELECTEDVALUE(MonthYearSelector[Year])
VAR SelectedMonth = SELECTEDVALUE(MonthYearSelector[Month])
VAR StartDate = DATE(SelectedYear, SelectedMonth - 1, 1)
VAR EndDate = EOMONTH(DATE(SelectedYear, SelectedMonth, 1), 0)
RETURN IF(DateTable[Date] >= StartDate && DateTable[Date] <= EndDate, 1, 0)
Build the Matrix Visual

Open the Matrix visual and add - Rows: WorkLog[Technician] , Columns: DateTable[Date], Values: SUM(WorkLog[Hours])

Add Filter to Show Only Relevant Dates

In the Filters pane, drag DateTable[ShowInMatrix] and set the filter to 1.

 

For example, if you select:
Year = 2025 , Month = 5 (May) .
The matrix will display columns from April 1, 2025 to May 31, 2025, adjusting automatically based on your selection.

Please find below attached .pbix file for your reference.

Regards,
Sreeteja