Handle Overlapping Dates in PowerPivot

[ellilock]

I am really stuck. I have a data model in PowerPivot 2013 with clients and entry dates and exit dates for specific programs. I need to find the overall length of time between entry and exit assuming that a client cannot physically be in more than one program concurrently and may leave for a period of time. The problem is that they can be enrolled in multiple programs concurrently. The calculations I've come up with are artificially inflating the length of overall time in any programs because of multiple enrollments. here is an example:


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Client[/TD]
[TD]Program[/TD]
[TD]Entry Date[/TD]
[TD]Exit Date[/TD]
[TD]Length of Stay[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]1/1/2014[/TD]
[TD]2/1/2016[/TD]
[TD]761[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]B[/TD]
[TD]1/1/2015 [/TD]
[TD]1/1/2016[/TD]
[TD]365[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]B[/TD]
[TD]2/1/2016[/TD]
[TD]3/25/2016[/TD]
[TD]53[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]C[/TD]
[TD]3/25/2016[/TD]
[TD]Null[/TD]
[TD]31[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[TD]2/1/2010[/TD]
[TD]2/9/2016[/TD]
[TD]2199[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]B[/TD]
[TD]2/20/2016[/TD]
[TD]Null[/TD]
[TD]65[/TD]
[/TR]
</tbody>[/TABLE]


The null values in the Exit Date column are substituted with the date of the report build which is 4/25/2016 to calculate the Length of Stay (LOS). I have a column that concatenates the client ID and the entry date to create a unique identifier for the entries. I have used MINX and MAXX with EARLIER to successfully return the first and last overall entries for each client but the cumulative LOS for each client should reflect only the LOS for the time in residence in the system rather than a sum of the individual programs or the time from the beginning to the end.

• Client 1 should have an LOS of 845 days in the system. The correct LOS would be from Program A and the second program B and program C, but ignore the overlapping enrollment in the first Program B enrollment.
• Client 2 should have an LOS of 2264 in the system because they were not in the system for a few days between the end of their time in program A and beginning of their time in program B.

Some clients have hundreds of individual enrollments of a day or so, also.

Any help would be greatly appreciated.

Thanks!
Elizabeth

 

[Ozeroth]

Hi Elizabeth,

You basically need to calculate the union of all dates a client is in any program.

Here is one way of doing it.

Note: In your example, your # days were calculated as Exit Date - Entry Date, so if Entry Date = Exit Date you would get Days in System = 0. My version below includes both Entry and Exit Date in the count, so if Entry Date = Exit Date then Days in System = 1. Easiest way to control which version of calculation is used would be by adjusting the Exit Date column or have an 'Exit Date - 1' column.

1. You must have a 'Date' table with dates at least covering the earliest Entry Date to latest Exit Date.
2. Leave 'Date' table unrelated to main data table (if related, measure would need slightly different definition).
3. I've called main data table ClientData.
4. Days in System can then be defined as follows. The main trick is to use GENERATE to create a list of dates for every Entry/Exit combination, then count unique dates from this list.
   

   =
   CALCULATE (
       COUNTROWS ( 'Date' ),
       GENERATE (
           SUMMARIZE ( ClientData, ClientData[Entry Date], ClientData[Exit Date] ),
           DATESBETWEEN ( 'Date'[Date], ClientData[Entry Date], ClientData[Exit Date] )
       )
   )

Owen

 

[ellilock]

Thank you so much for the direction! This was a huge help. It’s not quite right, though, because of the substitution of the if (entry=exit) then 1 day logic rather than 0 day logic. Should I just subtract 1 from the measure? Making it:

LOSReal: =CALCULATE (COUNTROWS (dCalendar),
GENERATE (
SUMMARIZE ( fRawData, fRawData[Functional Entry], fRawData[Functional Exit]),
DATESBETWEEN ( dCalendar[Dates], fRawData[Functional Entry], fRawData[Functional Exit]))) -1


I have a calculated column to return each client’s time in system (Real LOS) which is:

=[LOSReal] (FILTER(fRawData,fRawData[Entry Exit Client Id]=EARLIER(fRawData[Entry Exit Client Id])),fRawData[EntryID])

Notes:

· All dates are mm/dd/yyyy notation.
· Calendar table is dCalendar it is unrelated.
· The application is human services related and actually tracking nights rather than days
· The client data table is fRawData.
· The entry and exits are calculated columns Functional Entry and Functional Exit.
· Functional Entry backs entries between Midnight and 6am back 7 hours so they receive a night in the system
· Functional exit substitutes the report run date for null (currently enrolled clients).
· Both Functional Entry and Functional Exit truncate time values from the date and time for my previous calculation method so it reads as midnight to get all integer calculations

Entry and Exit logic: Clients checking in during the day and immediately exiting would correctly have an LOS of 0, while the one who entered at 3am, and left at 7am would correctly have and LOS of 1. So I do need it to correctly resolve to 0 for entry = exit cases because I'm backing up entries, if necessary in Functional Entry.

Thank you so much!

E

 

[Ozeroth]

Hi again,
Actually, you should just subtract one from [Functional Exit] within DATESBETWEEN.
That will ensure each block of dates has the correct length.

LOSReal: =
CALCULATE (
    COUNTROWS ( dCalendar ),
    GENERATE (
        SUMMARIZE ( fRawData, fRawData[Functional Entry], fRawData[Functional Exit] ),
        DATESBETWEEN (
            dCalendar[Dates],
            fRawData[Functional Entry],
            fRawData[Functional Exit] [COLOR=#ff0000][B]- 1[/B][/COLOR]
        )
    )
)

Thanks for the explanation, interesting scenario!

Owen