Forum Discussion

rnoyce's avatar
rnoyce
Frequent Visitor
8 years ago
Solved

Calculate time difference based on report filter

Hi all

 

I am struggling to work out how to create a measure that will do the following.  I have 2 columns - ArrivalDateTime and DepartureDateTime.  I need to calculate the difference between the two, based on a departure date window (typically a week).  If the arrival was before that week window, I don't want to calculate any time prior to the beginning of the window, so in the example below, using a departure time window of between 24/07/2017 00:00:00 and 01/08/2017 00:00:00, the Duration column is what I'm currently calculating whereas the "Duration I want" is what I hope to calculate (both calculated in days).

 

I knwo the syntax would be something like:

 

DepartureDateTime - IF(ArrivalDateTime<MIN(WindowDate),MIN(WindowDate),ArrivalDateTime)

 

But I don't know how to get MIN(WindowDate)?  It should come from the report filter that uses relative date filtering on DepartureDateTime

 

For info I'm using a SSAS 2014 Tabular model as my datasource.  Any help would be much appreciated.

 

ArrivalDateTimeDepartureDateTimeDurationDuration I want
13/07/2017 17:0624/07/2017 05:4410.530.24
15/07/2017 19:2124/07/2017 05:448.430.24
03/04/2017 17:4124/07/2017 12:00111.760.5
24/07/2017 18:1425/07/2017 12:230.760.76
23/07/2017 22:0025/07/2017 17:411.821.74
  • Anonymous's avatar
    Anonymous
    8 years ago

    HI rnoyce,

     

    >>But I don't know how to get MIN(WindowDate)?  It should come from the report filter that uses relative date filtering on DepartureDateTime

    Based on your requirement, you want to get the min range from filter, right?
    If this is a case, you can try to use minx/maxx and allselected function to get the specific date range.

     

    min_Date =
    MINX ( ALLSELECTED ( Table ), [DepartureDateTime] )
    
    max_Date =
    MAXX ( ALLSELECTED ( Table ), [DepartureDateTime] )
    

     

     

    Regards,

    XIaoxin Sheng

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    HI rnoyce,

     

    >>But I don't know how to get MIN(WindowDate)?  It should come from the report filter that uses relative date filtering on DepartureDateTime

    Based on your requirement, you want to get the min range from filter, right?
    If this is a case, you can try to use minx/maxx and allselected function to get the specific date range.

     

    min_Date =
    MINX ( ALLSELECTED ( Table ), [DepartureDateTime] )
    
    max_Date =
    MAXX ( ALLSELECTED ( Table ), [DepartureDateTime] )
    

     

     

    Regards,

    XIaoxin Sheng

    • rnoyce's avatar
      rnoyce
      Frequent Visitor

      Hi Anonymous

       

      That works perfectly - thanks for your help