Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
GFire
Helper I
Helper I

Matrix with dynamic column value.

Hello,

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

 

image1.png

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-sshirivolu
Community Support
Community Support

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

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.

 

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
Super User
Super User

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,

Sorry,..

 

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.