DateDiff depending on Date Slicer

[Anonymous]

I've got rows that have columns [FromTime] and [ToTime], so I'm try to create a column that shows DATEDIFF between these 2 columns, but I also want the DATEDIFF to take into account the slicer which is based off the [FromTime] column.

So if I have a [FromTime] of April 1, and a [ToTime] of April 10, but my slicer is filtering [FromTime] to show dates between April 8-12, then the DATEDIFF will only show 2 days (April 8 - April 10)

Here's some more examples of how it should calculate:

Date Filter	[FromTime]	[ToTime]	DATEDIFF
April 1- April 5	April 1	April 10	5
March 28 - April 5	April 1	April 10	5
April 9 - April 13	April 1	April 10	1

Hope this makes sense!

Thanks everyone!

[FreemanZ]

hi @Anonymous 

Supposing you have two tables like:

they are not related. 

1) try to plot a slicer with Dates[Date] column

2) try to plot a table visual with Data[From], Data[To] and a measure like:

Datediff = 
DATEDIFF(
    MAX(MAX(Data[From]),MIN(Dates[Date])),
    MIN(MAX(Data[To]),MAX(Dates[Date])),
    DAY
)+1

it worked like:

p.s. calculated columns are not responsive to slicer or any report visuals, so table visual with meausre is used here.