Forum Discussion
Calculation for matrix table based on 3 date range slicer
Hi conniedevina
Need 3 things:
1. Three disconnected Date slicer tables (one per range)
2. One helper table to force 3 rows in the table/matrix
3. Measures that read slicer selections and calculate per row
1) Create 3 Disconnected Date Tables (for slicers)
```DAX
Date Range 1 =
CALENDAR ( DATE(2023,1,1), DATE(2025,12,31) )
Date Range 2 =
CALENDAR ( DATE(2023,1,1), DATE(2025,12,31) )
Date Range 3 =
CALENDAR ( DATE(2023,1,1), DATE(2025,12,31) )
```
2) Create a Helper Table (forces 3 rows)
```DAX
Range Selector =
DATATABLE (
"Range ID", INTEGER,
"Range Name", STRING,
{
{ 1, "Date Range 1" },
{ 2, "Date Range 2" },
{ 3, "Date Range 3" }
}
)
```
3) Capture Selected Dates per Slicer
```DAX
Start Date =
SWITCH (
SELECTEDVALUE ( 'Range Selector'[Range ID] ),
1, MIN ( 'Date Range 1'[Date] ),
2, MIN ( 'Date Range 2'[Date] ),
3, MIN ( 'Date Range 3'[Date] )
)
End Date =
SWITCH (
SELECTEDVALUE ( 'Range Selector'[Range ID] ),
1, MAX ( 'Date Range 1'[Date] ),
2, MAX ( 'Date Range 2'[Date] ),
3, MAX ( 'Date Range 3'[Date] )
)
```
4) Dynamic Month Range Label (since data is monthly)
```DAX
Month Range Label =
VAR StartDt = [Start Date]
VAR EndDt = [End Date]
RETURN
FORMAT ( StartDt, "MMM yy" ) & " - " & FORMAT ( EndDt, "MMM yy" )
```
5) Range-Aware Measures
```DAX
Total Value (By Range) =
VAR StartDt = [Start Date]
VAR EndDt = [End Date]
RETURN
CALCULATE (
SUM ( FactTable[Value] ),
FILTER (
ALL ( DateTable ),
DateTable[Date] >= StartDt &&
DateTable[Date] <= EndDt
)
)
```
```DAX
GE 50 (By Range) =
VAR StartDt = [Start Date]
VAR EndDt = [End Date]
RETURN
CALCULATE (
SUM ( FactTable[Value] ),
FactTable[Value] >= 50,
FILTER (
ALL ( DateTable ),
DateTable[Date] >= StartDt &&
DateTable[Date] <= EndDt
)
)
```
```DAX
% GE 50 =
DIVIDE ( [GE 50 (By Range)], [Total Value (By Range)] )
``
Matrix visual
* Rows → `Range Selector[Range Name]`
* Values →
* `Month Range Label`
* `Total Value (By Range)`
* `GE 50 (By Range)`
* `% GE 50`
Slicers
* Date Range 1 → `Date Range 1[Date]`
* Date Range 2 → `Date Range 2[Date]`
* Date Range 3 → `Date Range 3[Date]
Hi conniedevina
Yes, this is possible in Power BI but it cannot be done with normal slicers alone.
You need a disconnected table + measures approach.
A single slicer cannot return 3 independent date ranges
Need to use 3 disconnected date range selectors
Measures that calculate results per range
A matrix/table driven by a helper table
1) Create a Date table
If you don’t already have one:
DateTable =
ADDCOLUMNS (
CALENDAR ( DATE(2024,1,1), DATE(2024,12,31) ),
"MonthYear", FORMAT ( [Date], "MMM yy" )
)Relate:
DateTable[Date] → FactTable[Month]
2) Create a Disconnected Range Table
This drives the matrix rows.
Date Ranges =
DATATABLE (
"Range Name", STRING,
"Range Start", DATE,
"Range End", DATE,
{
{ "Mar 24 - Dec 24", DATE(2024,3,1), DATE(2024,12,31) },
{ "Mar 24 - Oct 24", DATE(2024,3,1), DATE(2024,10,31) },
{ "Oct 24 - Dec 24", DATE(2024,10,1), DATE(2024,12,31) }
}
)
Do not create relationships for this table.
3) Base Measures
Total Value
Total Value =
SUM ( FactTable[Value] )
>= 50 Value
GE 50 Value =
CALCULATE (
SUM ( FactTable[Value] ),
FactTable[Value] >= 50
)
4) Range-Aware Measures (Core Logic)
Total (by selected range row)
Total (By Range) =
VAR StartDate = MIN ( 'Date Ranges'[Range Start] )
VAR EndDate = MAX ( 'Date Ranges'[Range End] )
RETURN
CALCULATE (
[Total Value],
FILTER (
ALL ( DateTable ),
DateTable[Date] >= StartDate &&
DateTable[Date] <= EndDate
)
)
>=50 (By Range)
GE 50 (By Range) =
VAR StartDate = MIN ( 'Date Ranges'[Range Start] )
VAR EndDate = MAX ( 'Date Ranges'[Range End] )
RETURN
CALCULATE (
[GE 50 Value],
FILTER (
ALL ( DateTable ),
DateTable[Date] >= StartDate &&
DateTable[Date] <= EndDate
)
)
Percentage
% GE 50 =
DIVIDE ( [GE 50 (By Range)], [Total (By Range)] )
Format as Percentage.
5) Build the Matrix
Rows → Date Ranges[Range Name]
Values
Total (By Range)
GE 50 (By Range)