Identify service episodes and group dates

[hpatel24779]

Hi All,

I would like to thank all of you who have helped me in the past and i am looking for additional help.

I am trying to identify episodes of service and identify the earliest start date and latest end date for each episode per client.

People can start and stop services so there will be multiple lines for individual service users. In totality, I want to know the earliest start and latest end date per episode per client. For example, a person has the following service lines:

Line 1 – Start 20/02/2017 and End date is null

Line 2 – Start 20/02/2017 and end date is 31/03/2020

Line 3 – Start 06/05/2019 and end date is 31/03/2020

Line 4 – Start 20/09/2023 and end date is null

Line 5 – Start date is 11/11/2024 and end date is 24/04/2025

So as you can see from the above, all dates are continuous with the earliest start being 20/02/2017 and still open so in a new column the start date should be 20/02/2017 and End date as null for each line.

Another example, client has

Line 1 - Start 07/04/2016 and end date 07/04/2016

Line 2 – Start 02/10/2016 and end date is 31/03/2020

Line 3 – Start 01/04/2020 and end date 28/07/2020

Line 4 – Start 28/01/2021 and end date is null

Line 5 – Start 22/03/2023 and end date is 14/02/2024

Line 6 – Start 12/12/2024 and end date is 31/03/2025

So in this scenario there are different gaps so I expect to see the following:

Line 1 – Start and end date as 07/04/2016

Line 2 & 3  – Start date 02/10/2016 and end date as 28/07/2020

Line 4,5 & 6 – Start date 28/01/2021 and end date as null

I have searched online but not getting the desired results. A lot require grouping rows and indexing but when using index, it only refers to previous row etc so I am hoping people have done something similar and have a solution.

I have also tried different methods such as running a report with all direct payments services ended on or after 01/04/2025 (All services). Then duplicating this report and changing report parameters to return those starting on or after 01/04/2025 (New services) and another report for those ended on or after 01/04/2025 (Ended services)

Then I have merged the new and ended cases to the DP All report and creating a column that identifies the earliest start and end dates based on the 3 dates. However still missing the piece of how to reference to previous rows easily.

My data contains many rows so have not been able to provide actual data for testing purposes but hope the above gives you an indication of what i am trying to achieve.

kind regards

Hetal

[v-karpurapud]

Hi @hpatel24779 

Thank you for reaching out to the Microsoft Fabric Community Forum.

I reproduced the scenario on my end using sample data and it worked successfully. To help you better understand the implementation, I’ve attached the .pbix file and screenshot for your reference. Please take a look at it and let me know your observations.

Thank you for being part of the Microsoft Fabric Community!

Regards,
Microsoft Fabric Community Support.

[hpatel24779]

hi @v-karpurapud 

sorry for delay in replying. I have had a look at the example PBI you have sent and not sure if it is displaying as expected.

I have created a table with all the fields and comparing the element dates with the episode dates.

For ID 7290, not an issue as the episode start and end is what i expect. However many of the others don't appear correct.

ID 24050 for example has 5 lines

Line 1 - Element start 01/11/2021 end date null

Line 2 - Element start 01/11/2021 end date 27/04/2025

Line 3 - Element start 08/05/2023 end date 27/04/2025

Line 4 - Element Start 29/04/2024 end 27/04/2025

Line 5 - Element Start 28/04/2025 end date null

For Lines 1-4 the episode start date is 01/11/2021 and end date is 27/04/2025 and Line 5 episode start is 28/04/2025 with end date null.

This is not what i expected. i would want to see the episode start as 01/11/2021 and end date as null as service user has a service open since 01/11/2021.

Another example, id 37493 who has 3 lines:

Line 1 - Start 01/04/2024 and end 27/04/2025

Line 2 - Start 01/04/2024 and end 29/05/2025

Line 3 - Start 28/04/2025 and end date null

The episode date shows start as 01/04/2024 and end date as 29/05/2025. The expected result is start 01/04/2024 and end date as null because there is still an open service and all dates are continuous.

I have found this issue where it works for some cases but not for others and it is frustrating.

I appeciate the help you have provided but unfortunately still not giving the desired results.

kind regards

Hetal

[v-karpurapud]

Hi @ronrsnfld 

Thanks for the follow up. I understand your concern. The intent of my follow-up was not to "nag," but to check whether the proposed solutions actually helped the OP. Confirming the resolution can benefit both the OP and future community members who may face the same issue, as it helps ensuring the thread remains complete and informative.

Regards,
Karpurapu D.

[ronrsnfld]

I understand your intent. However a Private Message to the OP would equally serve that purpose and would not pollute the thread with messages that do not help at all with solving the problem.

[v-karpurapud]

Hi @hpatel24779 

Thank you for submitting your question to the Microsoft Fabric Community Forum, and thanks to @ronrsnfld and @jgeddes  for offering helpful suggestions.

Could you let us know if the suggested solution resolved your issue? This information can assist other community members facing similar challenges.

Thank you.

[ronrsnfld]

Please explain how this nag helps to answer the question?

[hpatel24779]

Hi,

Does this link work?

https://1drv.ms/x/c/9eb0597c98b072c8/ETotvxO5OGdOnbOX3yiidqoBWUuEZzv-vRJmAg33xSBxVQ?e=xh1Fii

Kind regards 

Hetal

[ronrsnfld]

That link works ok.

But it seems to me that all of the examples you have provided in that link are continuous or overlapping and, since they all include a null  end date, would all have null as the transformed end date.

Is that really the case? Or am I missing something?

[jgeddes]

Here is one way this might work.
Starting with example data like this...

You can get the following result... 

With this code...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY9LDsMwCETvwtoS4eOkWdbXiHL/a3SIoUqqSkZC8DwzHAe9qZGglHXBkw09nW0u9GdhbIJely9hqM5rAPtfwFH7lFC7a/cwFpaLD8pZPfp+ISNzOW8hvt7b2kc8gXIBT/eR8Zxz2Aj/X08gjCWnUvFGxjNWrdzK4jNpEeEoGGsdUPY44PwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Client = _t, Line = _t, StartDate = _t, EndDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Client", type text}, {"Line", Int64.Type}, {"StartDate", type date}, {"EndDate", type date}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,#date(9999, 12, 31),Replacer.ReplaceValue,{"EndDate"}),
    #"Grouped Rows" = Table.Group(#"Replaced Value", {"Client"}, {{"AllRows", each _, type table [Client=nullable text, Line=nullable number, StartDate=nullable date, EndDate=nullable date]}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows", {{"AllRows", each Table.Sort(Table.Unpivot(_, {"StartDate", "EndDate"}, "Attribute", "Value"), {"Value", "Line"}), type table}}),
    #"Added Custom2" = Table.AddColumn(Custom1, "Custom", each let ListA = Table.SelectRows([AllRows], each [Attribute] = "EndDate")[Line], ListB = List.Numbers(1,List.Count(ListA), 1) in List.Transform(List.Numbers(0,List.Count(ListA),1), each if ListA{_} = ListB{_} then ListA{_} else null)),
    Custom2 = Table.TransformColumns(#"Added Custom2", {{"AllRows", each Table.Pivot(_, List.Distinct([Attribute]), "Attribute", "Value"), type table}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(Custom2, "AllRows", {"Line", "StartDate", "EndDate"}, {"Line", "StartDate", "EndDate"}),
    #"Added Custom" = Table.AddColumn(#"Expanded AllRows", "newEndDate", each if [Custom]{[Line]-1} = [Line] then [EndDate] else null, type date),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Custom", "EndDate"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"StartDate", type date}, {"Line", Int64.Type}})
in
    #"Changed Type1"

Hopefully this gets you pointed in the right direction. 

[hpatel24779]

Hi @jgeddes 

I need both start and end dates for all rows.

I will keep playing around and see where I get but appreciate your help.

Kind regards 

Hetal

[jgeddes]

Apologies, I misread the requirements.
Does this work for you?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZBRCsQwCETvku+CdWLS7Wd7jdL7X2PHJC7NUogg+nBmcl3pSEtSFgQrn27s0730Bf4WWbKyx/ojMqtIdWB/BYy19xPIz9vFhVW08U6ZwLwvDTmHL5PNj9dnG3u3p7wcwKx+DnsmY+hOtcyA9WkNggqfmWg+x1QjgC+6IhDJIGo9SxD+ZcoxImIYZMT7Cw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Client = _t, Line = _t, StartDate = _t, EndDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Client", type text}, {"Line", Int64.Type}, {"StartDate", type date}, {"EndDate", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Client"}, {{"AllRows", each _, type table [Client=nullable text, Line=nullable number, StartDate=nullable date, EndDate=nullable date]}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows", {{"AllRows", each Table.AddIndexColumn(_, "Index", 0,1,Int64.Type), type table}}),
    Custom2 = Table.TransformColumns(Custom1, {{"AllRows", each Table.AddColumn(_, "EndCont", (r)=> try if Number.From(r[EndDate] - [StartDate]{r[Index]+1}) < -1 then r[Index] else null otherwise r[Index], Int64.Type), type table}}),
    Custom3 = Table.TransformColumns(Custom2, {{"AllRows", each Table.AddColumn(Table.FillUp(_, {"EndCont"}), "EndIndex", (r)=> if r[EndCont] < List.PositionOf([EndDate], null, Occurrence.First) then r[EndCont] else List.PositionOf([EndDate], null, Occurrence.First)), type table}}),
    Custom5 = Table.TransformColumns(Custom3, {{"AllRows", each Table.AddColumn(_, "NewStartDate", (r)=> [StartDate]{List.PositionOf([EndIndex], r[EndIndex], Occurrence.First)}, type date), type table}}),
    Custom4 = Table.TransformColumns(Custom5, {{"AllRows", each Table.AddColumn(_, "NewEndDate", (r)=> [EndDate]{r[EndIndex]}, type date), type table}}),
    Custom6 = Table.TransformColumns(Custom4, {{"AllRows", each Table.RemoveColumns(_, {"Index", "EndCont", "EndIndex"}), type table}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(Custom6, "AllRows", {"Line", "StartDate", "EndDate", "NewStartDate", "NewEndDate"}, {"Line", "StartDate", "EndDate", "NewStartDate", "NewEndDate"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded AllRows",{{"Line", Int64.Type}, {"StartDate", type date}, {"EndDate", type date}, {"NewStartDate", type date}, {"NewEndDate", type date}})
in
    #"Changed Type1"

[hpatel24779]

hi @jgeddes 

Are you able to provide the example pbix. I am trying to amend the code you have given and seem to be going wrong somewhere.

Would appreciate if you can send the .pbix file and i can go through and see where i am going wrong.

kind regards

Hetal

[jgeddes]

Here you go. Hope it works for you.

[hpatel24779]

Hi @jgeddes 

From the screen shot provided, i think this is what i am expecting.

I will further update you when i have applied to code to my data.

Thanks

Hetal

[hpatel24779]

Hi,

Below is a link to file exported in Excel so hope this works

Service Episode File 

kind regards

Hetal

[ronrsnfld]

That link requires an account which I do not have. Perhaps you could post it on something like OneDrive, Google Drive, DropBox or other that allows easier downloads.