Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

DateDiff depending on Date Slicer

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 ...
FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
3 years ago

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.