Calculation for matrix table based on 3 date range slicer

[conniedevina]

Hi,

Currently I have the raw data like this

Month	Value	Value grouping
Jan-24	100	more than equals to 50
Feb-24	55	more than equals to 50
Mar-24	36	less than 50
Apr-24	23	less than 50
May-24	34	less than 50
Jun-24	47	less than 50
Jul-24	82	more than equals to 50
Aug-24	50	more than equals to 50
Sept-24	12	less than 50
Oct-24	94	more than equals to 50
Nov-24	44	less than 50
Dec-24	75	more than equals to 50

Then would like to have 3 date slicers like these selections below

Date Range 1: Mar 24 - Dec 24
Date Range 2: Mar 24 - Oct 24
Date Range 3: Oct 24 - Dec 24

and would like to have the matrix table/table showing like this, as % is 

% = [more than equals to 50] / [total]

	Total	more than equals to 50	%
Mar 24 - Dec 24	497	301	61%
Mar 24 - Oct 24	378	226	60%
Oct 24 - Dec 24	213	169	79%

the period is based on the date range 1-3 so it will be custom based on user selection, is it possible to have such a custom in power bi table?

Any help would be appreciated Thank you!!

[cengizhanarslan]

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.

[cengizhanarslan]

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.

[conniedevina]

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 

[cengizhanarslan]

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

[conniedevina]

@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.

[krishnakanth240 · Accepted solution]

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]

[conniedevina]

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

[krishnakanth240]

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.

[Irwan]

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.

[krishnakanth240]

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)