Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
138 | |
107 | |
104 | |
82 | |
63 |