How to check if current start and end dates are in previous period (start and end dates)

Question

I have a tricky problem I am working on. I've made several attempts at capturing the data but have been unsuccessful.

I have a table in Power Bi that looks like this:

The key is in ascending order as well as the StartDate field. I would like to achieve the results in the "Period Overlap Delta" field but have had trouble trying to figure it out.

Basically, I'd like to assign a value of zero to any period (startdate-enddate combination) that is IN a previous period and take the date difference of those "super" periods.

Here is the DAX to produce the table:

Cases = DATATABLE("Key", integer, "StartDate", datetime, "EndDate", datetime
        ,{
        {1, "01/01/2018", "01/10/2018"}
        , {2, "01/03/2018","01/03/2018"}
        , {3, "01/05/2018","01/07/2018"}
        , {4, "01/15/2018","01/16/2018"}
        , {5, "01/21/2018","01/24/2018"}
        , {6, "01/25/2018", "01/27/2018"}
        , {7, "01/25/2018","01/27/2018"}
        })

Thanks so much in advance!!!

Foxan Ng Foxan Ng · Accepted Answer · 2018-06-14T04:07:17

We need to know whether or not a certain row is overlapped by a previous row. By previous we mean the key is smaller than current row. By overlapped we mean the StartDate is earlier or equal to current row and EndDate is later or equal to current row, hence:

Overlapped = 
COUNTROWS(
    FILTER(
        'Cases',
        'Cases'[StartDate] <= EARLIER('Cases'[StartDate]) &&
        'Cases'[EndDate] >= EARLIER('Cases'[EndDate]) &&
        'Cases'[Key] < EARLIER('Cases'[Key])
    )
)

And with this we just need to wrap it up and calculate the number of days with the DATEDIFF function:

Period Overlap Delta = 
VAR Overlapped =
COUNTROWS(
    FILTER(
        'Cases',
        'Cases'[StartDate] <= EARLIER('Cases'[StartDate]) &&
        'Cases'[EndDate] >= EARLIER('Cases'[EndDate]) &&
        'Cases'[Key] < EARLIER('Cases'[Key])
    )
)
RETURN
IF(Overlapped, 0, DATEDIFF('Cases'[StartDate], 'Cases'[EndDate], DAY) + 1)

P.S. The use of DATATABLE to provide sample data is golden and should be promoted more often!

How to check if current start and end dates are in previous period (start and end dates)

1 Answers