Calculate number of days in period excluding days before or after period

[rnoyce]

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

[v-ljerr-msft · Accepted 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

[rnoyce]

Hi @v-ljerr-msft

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