Matrix with dynamic column value.

[GFire]

Hello,

I need the range of dates in the matrix column that extends to the month prior to the filter.

[Ashish_Mathur · Accepted solution]

Hi,

I have solved a similar question in the attached PBI file.

[v-sshirivolu]

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

[v-sshirivolu]

Hi @GFire ,

I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

[v-sshirivolu]

Hi @GFire ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you

[DataNinja777]

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,

[GFire]

Sorry,..

Only the month of May appears instead of the month of April.