Forum Discussion
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.
| 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 |
- Anonymous8 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
- AnonymousNot 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
- rnoyceFrequent Visitor
Hi Anonymous
That works perfectly - thanks for your help