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]
Thanks for reply, but to confirm the Date range 1-3 as mentioned it's a date range slicers it's not static month. Based I mentioned it's a date slicer, not static.
User can select any month as they want based on date range
and the table
So the table will always show as 3 rows since we only provide 3 date slicers selection.
Since the data showing as monthly possible to show as month range as rows?
And as sample, the month can be overlapping so it's not always different months for each rows.
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]
Thanks for answer and help, to add
I'm using table rather than Matrix visuals and hide the Range Name column since I only want to show starting from the month
Overall it works thanks
You're welcome conniedevina
Sure.
Could you please give kudos/heads-up if it has helped you, for other community members it will be useful for similar scenario.