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 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 5April 1April 105
March 28 - April 5April 1April 105
April 9 - April 13April 1April 101

 

Hope this makes sense!

 

Thanks everyone!

1 Reply

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