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]
hello conniedevina
i think the trick is defining the month group.
There are two ways,
1. the simpler way is you type each value in month column to define each group of month.
Month Group =
IF(
'Table 1'[Month]="Jan-24"||'Table 1'[Month]="Feb-24"||'Table 1'[Month]="Mar-24",
"Jan 24 - Mar 24",
IF(
'Table 1'[Month]="Apr-24"||'Table 1'[Month]="May-24"||'Table 1'[Month]="Jun-24",
"Apr 24 - Jun 24",
IF(
'Table 1'[Month]="Jul-24"||'Table 1'[Month]="Aug-24"||'Table 1'[Month]="Sept-24",
"Jul 24 - Sept 24",
IF(
'Table 1'[Month]="Oct-24"||'Table 1'[Month]="Nov-24"||'Table 1'[Month]="Dec-24",
"Oct 24 - Dec 24"
))))
Month Group =
var _Quarter = QUARTER('Table 2'[Month])
var _Max = MAXX(FILTER('Table 2',QUARTER('Table 2'[Month])=_Quarter),'Table 2'[Month])
var _Min = MINX(FILTER('Table 2',QUARTER('Table 2'[Month])=_Quarter),'Table 2'[Month])
Return
FORMAT(_Min,"MMM YY")&"-"&FORMAT(_Max,"MMM YY")
after re-define month group, the rest of calculation is sum and divide.
Total =
SUMX(
FILTER(
'Table 2',
'Table 2'[Month Group]=EARLIER('Table 2'[Month Group])
),
'Table 2'[Value]
)
More than equal 50 =
SUMX(
FILTER(
'Table 2',
'Table 2'[Month Group]=EARLIER('Table 2'[Month Group])&&
CONTAINSSTRING('Table 2'[Value grouping],"more than equals")
),
'Table 2'[Value]
)
% =
DIVIDE(
'Table 2'[More than equal 50],
'Table 2'[Total]
)
Hope this will help.