cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rnoyce Frequent Visitor
Frequent Visitor

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

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

 

arrivaldeparture.png

1 ACCEPTED SOLUTION

Accepted Solutions
v-ljerr-msft Super Contributor
Super Contributor

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

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. Smiley Happy

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

2 REPLIES 2
v-ljerr-msft Super Contributor
Super Contributor

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

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. Smiley Happy

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 Frequent Visitor
Frequent Visitor

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

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