locked
Length of service accross multiple service agreements RRS feed

  • Question

  •  

    Hi all,

    My data is in the form of service agreements, each of which have a start and end date.  End date can be blank for open agreements.  I also have Agreement ID,Customer ID, Agreement Type and Customer Type.
    Each customer can have multiple agreements, which can be contiguous or not.  They could even overlap, or there may be gaps of time in between agreements.  Agreements can close and be replaced for reasons that, for my analysis, I'd like to ignore, and translate the multiple agreements into what I'll call 'episodes' of service. 

    What I'd like to do, using DAX, is:
    a) Count up the number of 'episodes' each customer receives.  An episode may be a single agreement, or it may be several contiguous or overlapping agreements. 
    b) Calculate the average length of an episode (and be able to split that measure by Service type, Customer Type etc)

    A customer may still have more than one episode, so it's not just as simple as finding the earliest start dates and latest end dates for each customer.

    I'd also like to be able to put in an assumed parameter whereby if one agreement starts within, say, 30 days of the end of a previous agreement, it counts as the same episode, whereas if it's 31 days or more, it counts as a new episode.

    Here is 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     New 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
     

    Any help really appreciated!
    Giles

     

    Tuesday, June 7, 2016 10:11 AM

Answers

  • Thanks Michael - I'll give this a go today.

    Or am I missing some additional logic?
    No additional logic - that was me making a mistake! Cheers
    • Marked as answer by GilesRobinson Tuesday, June 7, 2016 3:29 PM
    Tuesday, June 7, 2016 12:23 PM

All replies

  • Hi Giles,

    You could try something like this...

    First add a calculated column to your table that we'll use to group agreements in the same episode:

    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
        )
      )
    )

    Now we can more easily add two measures that use this column to derive a) and b)....

    a) A calculated field/measure for the number of episodes per customer:

    Episodes := CALCULATE(DISTINCTCOUNT(Data[EndingAgreementID]))

    b) A calculated field/measure for the average episode length:

    Average Episode Length := 
    INT(
      AVERAGEX(
        ADDCOLUMNS(
          SUMMARIZE(
            Data,
            Data[ClientID],
            Data[EndingAgreementID]
          ),
          "EpisodeStartDate", CALCULATE(
                                MIN(Data[StartDate]), 
                                ALLEXCEPT(Data, Data[ClientID], Data[EndingAgreementID])
                              ),
          "EpisodeEndDate", CALCULATE(
                              MAX(Data[EndDate]), 
                              ALLEXCEPT(Data, Data[ClientID], Data[EndingAgreementID])
                            )
         ),
         IF(ISBLANK([EpisodeEndDate]), NOW(), [EpisodeEndDate]) - [EpisodeStartDate]
       )
    )

    According to your description of the logic, it looks like the comments value against agreement A4 isn't correct. A3 ends on 30 April 2015 and A4 starts on the 1 May 2015 so these fall within the 30 day threshold. Or am I missing some additional logic?


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    • Edited by Michael Amadi Tuesday, June 7, 2016 12:27 PM Added colons to measure DAX i.e. ':='
    • Marked as answer by GilesRobinson Tuesday, June 7, 2016 3:22 PM
    • Unmarked as answer by GilesRobinson Tuesday, November 8, 2016 10:31 AM
    Tuesday, June 7, 2016 11:54 AM
  • Thanks Michael - I'll give this a go today.

    Or am I missing some additional logic?
    No additional logic - that was me making a mistake! Cheers
    • Marked as answer by GilesRobinson Tuesday, June 7, 2016 3:29 PM
    Tuesday, June 7, 2016 12:23 PM
  • Seems to work, nice one Michael.  This has given me a really good introduction to a few DAX functions that I couldn't get my head around (EARLIER, ADDCOLUMNS, SUMMARIZE, ALLEXCEPT). 

    I may simplify things & remove the bit that handles blanks in the EndDate column, as my eventual dataset will deal only in completed episodes - this is a length of service analysis, and including ongoing services will skew things.

    Thanks again

    Tuesday, June 7, 2016 3:42 PM
  • Glad that it helped :)

    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Tuesday, June 7, 2016 4:09 PM
  • Hi guys,

    Spotted a problem with this - it doesn't seem to work right where there is a significant gap in the service.  Here's an example - it's a pretty complex one with a number of overlapping agreements, but for some reason the DAX given above groups several of them as A27316:

     

    Any help appreciated!

    Giles

    Tuesday, November 8, 2016 10:44 AM
  • Hi Giles,

    Sorry that I didn't see this sooner. 

    I can see that you've created a related thread here:

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/235b35cf-5819-445a-a66b-d7c385fb0ebd/earliest-episode-start-date?forum=sqlkjpowerpivotforexcel

    If needed, I can provide an alternative solution but it looks like Angelia Zhang's most recent response in the above post is more in-line with the behaviour you want. 



    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    • Edited by Michael Amadi Friday, December 9, 2016 7:50 AM Small edits
    Thursday, December 8, 2016 4:48 PM