DATEDIFF between 2 tables where ID repeats?

[alya1]

Hello,

I have 2 tables like below:

Start table

ID	Effective Day	Action
1	1/1/2001	Start
1	1/1/2005	Start
1	1/1/2013	Start
2	2/2/2005	Start
3	3/3/2006	Start

End table

ID	Effective Day	Action
1	1/1/2003	End
1	1/1/2010	End
2	2/5/2005	End

Is there a way to calculate the length of stay for each ID's individual residency?

Since 1 ID can start and leave multiple times (i.e. ID 1), I want to make sure datediff is grabbing the corresponding start nd end date. 

If there is no corresponding end date (i.e. ID 1's last start on 1/1/2013 and ID 3), I would like to use TODAY() for calculating the duration. 

Hopeful results:

ID	Effective Day	Action	Length
1	1/1/2001	Start	731 days
1	1/1/2005	Start	1827 days
1	1/1/2013	Start	4134 days
2	2/2/2005	Start	4 days
3	3/3/2006	Start	6630 days

End goal is to add a visual that shows our average length of stay.

Thank you!

[Jihwan_Kim · Accepted solution]

Hi,
I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

COALESCE function (DAX) - DAX | Microsoft Learn

DATEDIFF function (DAX) - DAX | Microsoft Learn

Length measure: =
VAR _currentid =
    SELECTEDVALUE ( 'Start'[ID] )
VAR _currentstartdate =
    SELECTEDVALUE ( 'Start'[Effective Day] )
VAR _enddate =
    MINX (
        FILTER (
            'End',
            'End'[ID] = _currentid
                && 'End'[Effective Day] > _currentstartdate
        ),
        'End'[Effective Day]
    )
RETURN
    IF (
        HASONEVALUE ( 'Start'[ID] ),
        DATEDIFF ( _currentstartdate, COALESCE ( _enddate, TODAY () ), DAY ) + 1
    )

[AnalyticsWizard]

@alya1 

To calculate the length of stay for each ID's individual residency, considering the dynamics of multiple starts and ends, including handling cases where there is no end date (using TODAY() for those scenarios), we can use Power BI's Power Query Editor to first align the start and end dates and then calculate the durations. Below are the steps to accomplish this:

Step 1: Merge Start and End Dates

1. Open Power Query Editor: Load both your Start and End tables into Power BI.
2. Sort Both Tables: Before merging, make sure to sort both tables by `ID` and `Effective Day`. This helps in correctly lining up the start and end dates for the same `ID`.
3. Merge the Tables: Create a new query where you merge the Start table with the End table on the `ID` field. Use a Left Outer join to ensure all starts have a corresponding end if available.
4. Create a Custom Column for Corrected End Dates: After merging, you'll have columns for Start Date and End Date. Add a custom column to calculate the end date:

if [End Date] = null then DateTime.LocalNow() else [End Date]

This formula checks if there's no End Date and substitutes it with today’s date.

Step 2: Calculate Duration for Each Stay
1. Calculate Duration: Add another custom column to calculate the duration between the start and the corrected end date:

Duration.Days([Corrected End Date] - [Start Date])

2. Handle Overlapping Periods: Since there could be overlapping periods where the previous end date after a start date, you need to ensure that the next start date is considered only after the last end date. You might need additional logic to filter out incorrect pairings depending on your data specifics.

Step 3: Load and Create Visuals
1. Load the Data: Once your query is ready, load the data back into Power BI.
2. Create a Visual: Use a suitable visual, like a bar chart, to represent the Length of Stay for each ID. You can also use average calculations to display the average length of stay.

If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

[Jihwan_Kim · Accepted solution]

Hi,
I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

COALESCE function (DAX) - DAX | Microsoft Learn

DATEDIFF function (DAX) - DAX | Microsoft Learn

Length measure: =
VAR _currentid =
    SELECTEDVALUE ( 'Start'[ID] )
VAR _currentstartdate =
    SELECTEDVALUE ( 'Start'[Effective Day] )
VAR _enddate =
    MINX (
        FILTER (
            'End',
            'End'[ID] = _currentid
                && 'End'[Effective Day] > _currentstartdate
        ),
        'End'[Effective Day]
    )
RETURN
    IF (
        HASONEVALUE ( 'Start'[ID] ),
        DATEDIFF ( _currentstartdate, COALESCE ( _enddate, TODAY () ), DAY ) + 1
    )

[alya1]

Thank you so much Jihwan! I really appreciate the tidy DAX, screenshots, and even links to the functions used. May I ask a follow up question of how to show the mean/median/mode of all length measure please? 

[Jihwan_Kim]

Hi, 

Thank you for your message.
I am not 100% sure if I understood your question correctly, but if it is OK with you, could you please share how the expected outcome looks like? 
Mean/Median/Mode for all values? Or, values per each ID?

[alya1]

Mean/Median/Mode for all values please. 

For example, I would like just a Card visual showing the Mean of all Length measures = 2669.2 

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a measure to calculated AVERAGE.