Calculation for matrix table based on 3 date range slicer

Irwan krishnakanth240 

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 

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)

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"
))))

 

2. if you have plenty of month, then it will become time consuming to type each value. Then you need to create a logic to define the max and min inside each quarter something like below.

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.

Thank you.

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

1. Use the [Range Name] column from the Header Table for the Rows of your Matrix visual.

2. Place the [Date Range Display] and all three Final Display measures (Total, Percent) into the Values field.

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]),
ALL(RawData, RawData[Value grouping]),
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

1. Use the [Range Name] column from the Header Table for the Rows of your Matrix visual.

2. Place the [Date Range Display] and all three Final Display measures (Total, Percent) into the Values field.