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'm struggling with a way to calculate the number of days something has stayed within a period. If the arrival date is before the period start date, I need to use the period start date as the start date of the calculation - similarly if the departure date is after the period end date, use the period end date as the end date of the calculation. An example of the data (including the desired outcome under VisitDuration During Period) is below.
I have related and unrelated date tables that can be used. I'm sure the answer will be to count the rows from a date table, but can't find a way to filter the date table based on arrival date / period start, and departure date / period end
Thanks
Solved! Go to Solution.
Hi @rnoyce,
If I understand you correctly, you should be able to use the formula below to create a measure calculate the duration days in your scenario.
Measure = VAR startDate = MAX ( MIN ( Table1[ArrivalDateTime] ), MIN ( 'Date'[Date] ) ) VAR endDate = MIN ( MAX ( Table1[DepartureDateTime] ), MAX ( 'Date'[Date] ) ) RETURN IF ( endDate >= startDate, DATEDIFF ( startDate, endDate, DAY ) )
Note: You'll need to replace the bold with your real table name, and column name.
Regards
Hi @rnoyce,
If I understand you correctly, you should be able to use the formula below to create a measure calculate the duration days in your scenario.
Measure = VAR startDate = MAX ( MIN ( Table1[ArrivalDateTime] ), MIN ( 'Date'[Date] ) ) VAR endDate = MIN ( MAX ( Table1[DepartureDateTime] ), MAX ( 'Date'[Date] ) ) RETURN IF ( endDate >= startDate, DATEDIFF ( startDate, endDate, DAY ) )
Note: You'll need to replace the bold with your real table name, and column name.
Regards
Thanks for the response - You have understood correctly, and the logic to that solution looks like it will work perfectly for what I need. I failed to mention in my post however, that I'm not yet on 2016 version (will be soon). It appears that my DAX expressions can't use variables or expressions in the MIN and MAX functions until I've upgraded - will test the solution then!
Thanks again
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 |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |