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, Thanks for your suggestions but your first calculation somehow it give me wrong result...
and also for final measure and date range display it can't be shown
For Date Range Display I changed to below and working
Date Range Display =
SWITCH(
TRUE(),
SELECTEDVALUE('Range Header'[Range Name]) = "Date Range 1",
FORMAT(MIN('Calendar1'[Date]), "MMM yy") & " - " & FORMAT(MAX('Calendar1'[Date]), "MMM yy"),
SELECTEDVALUE('Range Header'[Range Name]) = "Date Range 2",
FORMAT(MIN('Calendar2'[Date]), "MMM yy") & " - " & FORMAT(MAX('Calendar2'[Date]), "MMM yy"),
SELECTEDVALUE('Range Header'[Range Name]) = "Date Range 3",
FORMAT(MIN('Calendar3'[Date]), "MMM yy") & " - " & FORMAT(MAX('Calendar3'[Date]), "MMM yy"),
BLANK()
)But not sure how to debug the first calculation and the final, somehow keeps getting me error
And also for the table we don't need to show the red color, I just put in red color as notes.The things I want to show only based on the post which is
Could you try this:
Total Range 1 =
VAR MinDate = MIN('Slicer Date 1'[Date])
VAR MaxDate = MAX('Slicer Date 1'[Date])
RETURN
CALCULATE(
SUM(RawData[Value]),
FILTER ( ALL(RawData),
RawData[Month] >= MinDate &&
RawData[Month] <= MaxDate
) )