calculate datediff (Total days) and it should not be affected by another slicer

[mdaatifraza5556]

Good morning,

I am facing an issue while calculating the total days between the selected dates using the range slicer.

The relationship between the tables is bidirectional, with the Reports table having a one-to-many relationship with the Reason table.

Slicers I am using:

• Location, Reason Name, and Start Time from the Reason table
• Grade from the Reports table

The below DAX formula I am using works fine, it is not affected by Location and Reason when I set the date in the range slicer. However, it is affected by Grade i want that that it it should not be affected by Grade as well..

Please help me resolve this issue.

Thank you

Hrs_In =

VAR StartDate =

    CALCULATE(

        MIN(tblDownTimeReport[StartTime]),

        CROSSFILTER(tblDownTimeReport[Id], DownTimeReportReason[DownTimeId], NONE),

        REMOVEFILTERS(tblDownTimeReport[GradeCode])

    )

VAR EndDate =

    CALCULATE(

        MAX(tblDownTimeReport[StartTime]),

        CROSSFILTER(tblDownTimeReport[Id], DownTimeReportReason[DownTimeId], NONE),

        REMOVEFILTERS(tblDownTimeReport[GradeCode])

    )

VAR MonthsInRange =

    SELECTCOLUMNS(

        FILTER(

            ALL(tblDownTimeReport),

            tblDownTimeReport[StartTime] >= StartDate &&

            tblDownTimeReport[StartTime] <= EndDate

        ),

        "MonthStart", tblDownTimeReport[StartTime]

    )

VAR MinDate = MINX(MonthsInRange, [MonthStart])

VAR MaxDate = MAXX(MonthsInRange, [MonthStart])

VAR _Days = DATEDIFF(MinDate, MaxDate, DAY) + 1

RETURN _Days

[v-sdhruv]

Hi @mdaatifraza5556 ,
Glad to know you were able to resolve the issue.
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[mdaatifraza5556]

Thank you so much for the reply.
I got my solution.
I was facing the issue due to data modelling.

[bhanu_gautam]

@mdaatifraza5556 , try using

DAX
Hrs_In =
VAR StartDate =
CALCULATE(
MIN(tblDownTimeReport[StartTime]),
CROSSFILTER(tblDownTimeReport[Id], DownTimeReportReason[DownTimeId], NONE),
REMOVEFILTERS(tblDownTimeReport[GradeCode])
)

VAR EndDate =
CALCULATE(
MAX(tblDownTimeReport[StartTime]),
CROSSFILTER(tblDownTimeReport[Id], DownTimeReportReason[DownTimeId], NONE),
REMOVEFILTERS(tblDownTimeReport[GradeCode])
)

VAR MonthsInRange =
SELECTCOLUMNS(
FILTER(
ALL(tblDownTimeReport),
tblDownTimeReport[StartTime] >= StartDate &&
tblDownTimeReport[StartTime] <= EndDate
),
"MonthStart", tblDownTimeReport[StartTime]
)

VAR MinDate = MINX(MonthsInRange, [MonthStart])
VAR MaxDate = MAXX(MonthsInRange, [MonthStart])

VAR _Days = DATEDIFF(MinDate, MaxDate, DAY) + 1

RETURN _Days

[mdaatifraza5556]

Hi @bhanu_gautam 
Here are samples, kindly help me with this I think if iIget solved on this, then it will work

below i am providing some sample what i want is. 
I want total days when i am selecting start and end date in range slicer then it gives me total days and that total days should not be affected by grade slicer if selecting any one of the grade

Statrt Date	GradeCode
01-03-2025	211287
01-03-2025	211287
02-03-2025	84693
02-03-2025	84693
02-03-2025	84693
03-03-2025	257533
03-03-2025	257533
03-03-2025	257533
03-03-2025	212202
03-03-2025	257533
04-03-2025	267520
04-03-2025	267520
04-03-2025	267520
05-03-2025	257533

[bhanu_gautam · Accepted solution]

@mdaatifraza5556 Update it using ALL

dax
Hrs_In =
VAR StartDate =
CALCULATE(
MIN(tblDownTimeReport[StartTime]),
CROSSFILTER(tblDownTimeReport[Id], DownTimeReportReason[DownTimeId], NONE),
ALL(tblDownTimeReport[GradeCode])
)

VAR EndDate =
CALCULATE(
MAX(tblDownTimeReport[StartTime]),
CROSSFILTER(tblDownTimeReport[Id], DownTimeReportReason[DownTimeId], NONE),
ALL(tblDownTimeReport[GradeCode])
)

VAR MonthsInRange =
SELECTCOLUMNS(
FILTER(
ALL(tblDownTimeReport),
tblDownTimeReport[StartTime] >= StartDate &&
tblDownTimeReport[StartTime] <= EndDate
),
"MonthStart", tblDownTimeReport[StartTime]
)

VAR MinDate = MINX(MonthsInRange, [MonthStart])
VAR MaxDate = MAXX(MonthsInRange, [MonthStart])

VAR _Days = DATEDIFF(MinDate, MaxDate, DAY) + 1

RETURN _Days

[mdaatifraza5556]

Thanks for your reply, but it is still getting affected by the grade code slicer


at the same time am using this DAX it also working same

 StartDate =

 var start_date = CALCULATE(

     MIN(tblDownTimeReport[StartTime]),

     ALLSELECTED(tblDownTimeReport[StartTime]),  

     ALL(tblDownTimeReport[GradeCode]),  

     CROSSFILTER(tblDownTimeReport[Id], DownTimeReportReason[DownTimeId], NONE)

 )

 VAR end_date =

  CALCULATE(

     MAX(tblDownTimeReport[StartTime]),

     ALLSELECTED(tblDownTimeReport[StartTime]),  

     ALL(tblDownTimeReport[GradeCode]),  

     CROSSFILTER(tblDownTimeReport[Id], DownTimeReportReason[DownTimeId], NONE)

 )

 VAR _days = DATEDIFF(start_date, end_date, DAY)+1

 RETURN

     _days