Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello,
I need the range of dates in the matrix column that extends to the month prior to the filter.
Solved! Go to Solution.
Hi,
I have solved a similar question in the attached PBI file.
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
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.
User | Count |
---|---|
70 | |
70 | |
34 | |
23 | |
22 |
User | Count |
---|---|
96 | |
94 | |
50 | |
42 | |
40 |