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]
1. Model Setup: Disconnected Tables
Disconnected Slicers: Create three copies of your primary date table (Slicer Date 1, 2, 3). Crucially, ensure they have NO active relationship with your RawData table. These will drive your slicers.
Header Table: Create a manual table (Range Header) with one column containing the text labels for your rows (e.g., "Date Range 1", "Date Range 2", "Date Range 3")
2. DAX Measures: Independent Filtering (Base Measures)
Create three sets of measures. These capture the dates selected by the disconnected slicers (MIN/MAX) and manually filter the RawData table.
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
) )
3. DAX Measures: Visual Display (SWITCH Logic)
These measures use the Range Header rows to select which base measure (Range 1, 2, or 3) to display.
Date Range Display =
SWITCH(
TRUE(),
VALUES('Range Header'[Range Name]) = "Date Range 1",
FORMAT(MIN('Slicer Date 1'[Date]), "MMM yy") & " - " & FORMAT(MAX('Slicer Date 1'[Date]), "MMM yy"),
VALUES('Range Header'[Range Name]) = "Date Range 2",
FORMAT(MIN('Slicer Date 2'[Date]), "MMM yy") & " - " & FORMAT(MAX('Slicer Date 2'[Date]), "MMM yy"),
VALUES('Range Header'[Range Name]) = "Date Range 3",
FORMAT(MIN('Slicer Date 3'[Date]), "MMM yy") & " - " & FORMAT(MAX('Slicer Date 3'[Date]), "MMM yy"),
BLANK()
)
-- Final Measure 2: Consolidated Total Value
Final Total Display =
SWITCH(
VALUES('Range Header'[Range Name]),
"Date Range 1", [Total Range 1],
"Date Range 2", [Total Range 2],
"Date Range 3", [Total Range 3],
BLANK()
)
4. Visual Construction
Use the [Range Name] column from the Header Table for the Rows of your Matrix visual.
Place the [Date Range Display] and all three Final Display measures (Total, Percent) into the Values field.
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
) )