Want to calculate difference between dates entered in Slicer even those records not present in Table

[AshDil]

Hi,

I have the date range slicer as follows,

In date slicer I have selected Start date = 5/1/2017 and End date  = 3/16/2018. As these dates are not present in my fact table I'm getting difference as blank.

Please help me to do it.

Thanks,

AshDil.

[amitchandak]

@AshDil , Ideally you should use a date table wih all the needed dates and join that with you table

measure =
var _max = maxx(allselected('Date'),'Date'[Date])
var _min = minx(allselected('Date'),'Date'[Date])
return

datediff(_min, _max, day)

[AshDil]

Hi @amitchandak ,

I have tried using above formula but getting large difference.

For eg, I have selected Start date = 11/22/2021 and End Date = 11/30/2021 my expected output is 8, but getting difference as 1461.

Please help me do it.

Thanks,

Ashdil

[v-rzhou-msft · Accepted solution]

Hi @AshDil 

If you want to calculate the day differences by dates in your date between slicer, you need to use min and max function to catch the start date and the end date in your data model. So you need to create a date table by dax, and relate two tables by date column, then create slicer by date column in date table.

Date Table:

Date = CALENDARAUTO()

Relationship:

Measure:

DayDiff = 
VAR _RangeStart = MIN('Date'[Date])
VAR _RangeEnd = MAX('Date'[Date])
RETURN
DATEDIFF(_RangeStart,_RangeEnd,DAY)

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.