locked
Earliest episode start date RRS feed

  • Question

  • Hi guys,

    I had a really helpful response to my last question here, but need further help.

    Here's the previous post:

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b53c79b3-8c2e-401b-b351-499622035bac/length-of-service-accross-multiple-service-agreements?forum=sqlkjpowerpivotforexcel

    It allowed me to group Agreements (the data I have) into Episodes (the data I want), by badging the "ending agreement ID" against each agreement. 

    Once again, I have agreements data, each client can have multiple agreements which can be contiguous or have gaps, and can overlap.  Here's a sample of my data:

    ClientID AgreementID AgreementType StartDate EndDate EndReason Comments
    Client1 A1 HomeCare 10/01/2015 20/01/2015 Change This   is one episode
    Client1 A2 HomeCare 21/01/2015 28/02/2015 Hospital …same episode
    Client1 A3 HomeCare 31/03/2015 30/04/2015 AdmittedToRes …still   the same episode
    Client1 A4 Nursing 01/05/2015     ...still the same episode
    Client2 A5 HomeCare 10/03/2015 30/03/2015 Change Client 2's first episode
    Client2 A6 HomeCare 10/04/2015 30/04/2015 Hospital Client 2's first episode continued
    Client2 A7 HomeCare 31/05/2015 10/06/2015 Change Start   of episode 2 for client 2
    Client2 A8 HomeCare 11/06/2015     Client 2's episode 2 continues
    Client3 A9 Nursing 01/03/2015 30/03/2015 Hospital Client   3's first episode
    Client3 A10 Nursing 01/08/2015     Client 3's second episode
     

    And here is the DAX that I was (very helpfully) given to group agreements ending within 31 days of each other into the same episode, using a calculated column:

    EndingAgreementID = 
    IF(
      ISBLANK(
        CALCULATE(
          LASTNONBLANK(Data[AgreementID], 1), 
          FILTER(
            ALLEXCEPT(Data, Data[ClientID]), 
            Data[StartDate] - EARLIER(Data[EndDate]) < 31))
        ), 
      Data[AgreementID],  
      CALCULATE(
        LASTNONBLANK(Data[AgreementID], 1), 
        FILTER(
          ALLEXCEPT(Data, Data[ClientID]), 
          Data[StartDate] - EARLIER(Data[EndDate]) < 31
        )
      )
    )

    I'm now trying to modify it to give, in each case, the earliest agreement start date that forms a part of each episode. I've been trying variations on the following:

    =CALCULATE(
         FIRSTNONBLANK(Agreement[AgreementID],1), 
          FILTER(
            ALLEXCEPT(Agreement,Agreement[ClientID]), 
            Agreement[StartDate] - EARLIER(Agreement[EndDate],1) < 31))

    This doesn't give the results I want, and the 31 day threshold no longer works...getting some strange results.  I suspect the problem lies in my understanding (or not) of the EARLIER and FIRSTNONBLANK functions and how they iterate over which table.

    Any assistance much appreciated!

    Giles


    Monday, November 7, 2016 3:39 PM

Answers

  • I think the easiest thing to do is to build on top of the existing calculated column you created and then just find the minimum starting date within that group.

    eg.

    =minx( filter(data, Data[EndingAgreementID] = EARLIER(Data[EndingAgreementID])) , Data[StartDate])


    http://darren.gosbell.com - please mark correct answers

    Tuesday, November 8, 2016 6:27 AM
  • Hi GilesRobinson,

    I try to reproduce the scenario and get the expected result. Please review the following steps.

    First, create calculated columns below.

    Rank= RANKX('Date','Date'[AgreementID],,1)
    Previou EndDate=LOOKUPVALUE('Date'[EndDate],'Date'[Rank],'Date'[Rank]-1)
    End-Date=IF(ISBLANK('Date'[Previous EndDate]), 'Date'[EndDate],'Date'[Previous EndDate])
    
    
    Then create the first and last AgreementID, and calculate the result using the First and Last columns.

    First=CALCULATE(
         FIRSTNONBLANK('Date'[AgreementID],1),
               FILTER(
            ALLEXCEPT('Date', 'Date'[SubjectID]), 
            'Date'[StartDate] - 'Date'[End-Date] < 31))
    
    Last=CALCULATE(
         LASTNONBLANK('Date'[AgreementID],1),
               FILTER(
            ALLEXCEPT('Date', 'Date'[SubjectID]), 
            'Date'[StartDate] - 'Date'[End-Date] < 31))
    Result=IF(ISBLANK('Date'[Previous EndDate]),'Date'[First],'Date'[Last])


    I will update the information if I find better solution. Thanks a lot.

    Best Regards,
    Angelia



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 11, 2016 2:42 PM
  • Hi GilesRobinson,

    In your previous post, in the highlighted in yellow background, why the Eisode ID here should be A1989265? Based on my understanding, the startdate(2014/12/08) substract the previous EndDate(2005/10/24), the result is bigger 30, so it should be A1555236. In addtion, if the earlier enddate is blank, how to deal with it?


    Could you please describe the question for further analysis? Sorry for inconvenience.

    Thanks,
    Angelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 10, 2016 2:46 AM

All replies

  • I think the easiest thing to do is to build on top of the existing calculated column you created and then just find the minimum starting date within that group.

    eg.

    =minx( filter(data, Data[EndingAgreementID] = EARLIER(Data[EndingAgreementID])) , Data[StartDate])


    http://darren.gosbell.com - please mark correct answers

    Tuesday, November 8, 2016 6:27 AM
  • Thanks for the reply Darren (& that's helped my understanding of the EARLIER function - was reading on PowerPivotPro about how EARLIER is often best thought of as CURRENT ROW).

    However, I've been looking more carefully and actually the problem seems to be with my original calculated column.  It's not returning the correct values & never was.  Unless anybody has another suggestion I'm going to go back to the drawing board on this & find another approach.  Might revisit the original post & try to explain myself better!

    The other issue is that we're on a 32 bit build here and the volumes of data I have preclude me using AgreementID is v. memory hungry.  I was having to process my agreements data into episodes in batches of around 1000 to get round this.

    Giles

    Tuesday, November 8, 2016 9:31 AM
  • Hi GilesRobinson,

    In your previous post, in the highlighted in yellow background, why the Eisode ID here should be A1989265? Based on my understanding, the startdate(2014/12/08) substract the previous EndDate(2005/10/24), the result is bigger 30, so it should be A1555236. In addtion, if the earlier enddate is blank, how to deal with it?


    Could you please describe the question for further analysis? Sorry for inconvenience.

    Thanks,
    Angelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 10, 2016 2:46 AM
  • Apologies Angelia.  You're right about That was the end of a long day, and perhaps a needlessly complex set of example data.  I can handle blank end dates by putting a default value in.

    Here's a simpler scenario, starting afresh.  Here there are no overlaps between the agreement, one client and two episodes.

    I want to populate the Episode Start Date and Episode End Date columns, (not really fussed about the Episode ID columns unless they are a necessary interim step.) 

    I can then calculate the true number of episode starts using a DISTINCTCOUNT measure.

     I'd be just as happy to do this in a measure without the calc columns if possible.

    Once again, any help appreciated.

    Giles

     

    Thursday, November 10, 2016 5:03 PM
  • Hi GilesRobinson,

    I try to reproduce the scenario and get the expected result. Please review the following steps.

    First, create calculated columns below.

    Rank= RANKX('Date','Date'[AgreementID],,1)
    Previou EndDate=LOOKUPVALUE('Date'[EndDate],'Date'[Rank],'Date'[Rank]-1)
    End-Date=IF(ISBLANK('Date'[Previous EndDate]), 'Date'[EndDate],'Date'[Previous EndDate])
    
    
    Then create the first and last AgreementID, and calculate the result using the First and Last columns.

    First=CALCULATE(
         FIRSTNONBLANK('Date'[AgreementID],1),
               FILTER(
            ALLEXCEPT('Date', 'Date'[SubjectID]), 
            'Date'[StartDate] - 'Date'[End-Date] < 31))
    
    Last=CALCULATE(
         LASTNONBLANK('Date'[AgreementID],1),
               FILTER(
            ALLEXCEPT('Date', 'Date'[SubjectID]), 
            'Date'[StartDate] - 'Date'[End-Date] < 31))
    Result=IF(ISBLANK('Date'[Previous EndDate]),'Date'[First],'Date'[Last])


    I will update the information if I find better solution. Thanks a lot.

    Best Regards,
    Angelia



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 11, 2016 2:42 PM