Calculate time difference based on report filter

[rnoyce]

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.

ArrivalDateTime	DepartureDateTime	Duration	Duration I want
13/07/2017 17:06	24/07/2017 05:44	10.53	0.24
15/07/2017 19:21	24/07/2017 05:44	8.43	0.24
03/04/2017 17:41	24/07/2017 12:00	111.76	0.5
24/07/2017 18:14	25/07/2017 12:23	0.76	0.76
23/07/2017 22:00	25/07/2017 17:41	1.82	1.74

[v-shex-msft · Accepted solution]

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]

Hi @v-shex-msft

That works perfectly - thanks for your help