Forum Discussion

alya1's avatar
alya1
Icon for Helper V rankHelper V
2 years ago
Solved

DATEDIFF between 2 tables where ID repeats?

Hello,

 

I have 2 tables like below:

Start table

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

End table

ID Effective Day Action 
11/1/2003End
11/1/2010End
22/5/2005End

 

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
11/1/2001Start731 days
11/1/2005Start1827 days
11/1/2013Start4134 days
22/2/2005Start4 days
33/3/2006Start6630 days

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

Thank you!

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    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
    )

     

     

     

     

  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    It is for creating a measure to calculated AVERAGE.

     

     

6 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      alya1
      Icon for Helper V rankHelper V
      2 years ago

      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's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        2 years ago

        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?

  • AnalyticsWizard's avatar
    AnalyticsWizard
    Icon for Solution Supplier rankSolution Supplier
    2 years ago

    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 👍