Forum Discussion
Helper IVDynamic matrix Visual
Hi
I need to create 3 single month view matrix tables that change depending on the date selected from a slicer.
So the first will be the month of the start of the date selected and the following 2 will be the following 2 months.
The date field and values are all within the same data table
I have the following measures but not sure how to apply to the table:
Month1 Start = Min('Table' [Date])
Month 1 End = EOMonth(Min('Table' [Date]),0)
This I have then replicated for months 2 and 3 not sure how to apply them to the matrix visuals
So for example I need if the date selected is the 10th December 2026 then the 3 visuals will show December 26, January 27 and February 27.
| Date | Total Complete | Total Orders |
| 01/12/26 | 10 | 15 |
| 02/12/26 | 20 | 30 |
| 03/12/26 | 10 | 10 |
| 04/12/26 | 7 | 15 |
Hi maurcoll,
I would keep the existing measures fairly simple. The main thing is that you don't apply the Start/End measures directly to the matrix. Instead, use a measure as a visual-level filter.
Also, if the slicer is using the same Date column as the matrix, selecting 10 December will filter the matrix to only 10 December. So ideally, use a separate Calendar table for the slicer and matrix dates.
For example, create these measures:
Show Month 1 =
VAR SelectedDate = MIN('Calendar'[Date])
VAR CurrentDate = MAX('Table'[Date])
VAR StartDate = DATE(YEAR(SelectedDate), MONTH(SelectedDate), 1)
VAR EndDate = EOMONTH(SelectedDate, 0)
RETURN
IF(CurrentDate >= StartDate && CurrentDate <= EndDate, 1, 0)Show Month 2 =
VAR SelectedDate = MIN('Calendar'[Date])
VAR CurrentDate = MAX('Table'[Date])
VAR StartDate = EOMONTH(SelectedDate, 0) + 1
VAR EndDate = EOMONTH(SelectedDate, 1)
RETURN
IF(CurrentDate >= StartDate && CurrentDate <= EndDate, 1, 0)Show Month 3 =
VAR SelectedDate = MIN('Calendar'[Date])
VAR CurrentDate = MAX('Table'[Date])
VAR StartDate = EOMONTH(SelectedDate, 1) + 1
VAR EndDate = EOMONTH(SelectedDate, 2)
RETURN
IF(CurrentDate >= StartDate && CurrentDate <= EndDate, 1, 0)Then put 'Table'[Date] in the Rows of each matrix and add the relevant measure to the visual-level filters:
Matrix 1 → Show Month 1 = 1
Matrix 2 → Show Month 2 = 1
Matrix 3 → Show Month 3 = 1Your existing Total Complete and Total Orders measures can then remain as they are.
So if the slicer is set to 10/12/2026, you would get:
Matrix 1 → December 2026
Matrix 2 → January 2027
Matrix 3 → February 2027The separate Calendar table is important here. It allows the slicer to select 10 December without filtering the fact table down to only that one date, so the matrices can still show all the dates in December, January and February.
2 Replies
- ShahRukhSameer
Responsive Resident
Hi maurcoll,
I would keep the existing measures fairly simple. The main thing is that you don't apply the Start/End measures directly to the matrix. Instead, use a measure as a visual-level filter.
Also, if the slicer is using the same Date column as the matrix, selecting 10 December will filter the matrix to only 10 December. So ideally, use a separate Calendar table for the slicer and matrix dates.
For example, create these measures:
Show Month 1 =
VAR SelectedDate = MIN('Calendar'[Date])
VAR CurrentDate = MAX('Table'[Date])
VAR StartDate = DATE(YEAR(SelectedDate), MONTH(SelectedDate), 1)
VAR EndDate = EOMONTH(SelectedDate, 0)
RETURN
IF(CurrentDate >= StartDate && CurrentDate <= EndDate, 1, 0)Show Month 2 =
VAR SelectedDate = MIN('Calendar'[Date])
VAR CurrentDate = MAX('Table'[Date])
VAR StartDate = EOMONTH(SelectedDate, 0) + 1
VAR EndDate = EOMONTH(SelectedDate, 1)
RETURN
IF(CurrentDate >= StartDate && CurrentDate <= EndDate, 1, 0)Show Month 3 =
VAR SelectedDate = MIN('Calendar'[Date])
VAR CurrentDate = MAX('Table'[Date])
VAR StartDate = EOMONTH(SelectedDate, 1) + 1
VAR EndDate = EOMONTH(SelectedDate, 2)
RETURN
IF(CurrentDate >= StartDate && CurrentDate <= EndDate, 1, 0)Then put 'Table'[Date] in the Rows of each matrix and add the relevant measure to the visual-level filters:
Matrix 1 → Show Month 1 = 1
Matrix 2 → Show Month 2 = 1
Matrix 3 → Show Month 3 = 1Your existing Total Complete and Total Orders measures can then remain as they are.
So if the slicer is set to 10/12/2026, you would get:
Matrix 1 → December 2026
Matrix 2 → January 2027
Matrix 3 → February 2027The separate Calendar table is important here. It allows the slicer to select 10 December without filtering the fact table down to only that one date, so the matrices can still show all the dates in December, January and February.
- danextian
Super User
Hi maurcoll
You will need to use a disconnected table for the selected period as using a table with an active relationship to the fact table will filter the visible rows only to what's been selected. For example, if you select Dec only Dec becomes visible.
Please see the attached pbix.
