Creating a Episode reference with DAX based on start and end dates across multiple rows

[CJLewis87]

Hi All

I am trying to use DAX to create a unique episode reference for each row that is part of a single episode. As a starting point I have already created unique Index in Power Query for each person as defined by their ID number before loading into my report.

An episode should be a collection of all rows for the same Index number whereby the Finance Start Date is either the same as the previous rows Finance End Date or +1 days. So for Index 0, I want all 3 of those rows to be assigned the same ID number as the dates are consectutive from 04/01/18 to 21/02/18. If there was then another entry with a break in dates, I'd want a new reference creating and so on and so forth.

Many thanks in advance!

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[CJLewis87]

https://drive.google.com/file/d/1qoQKlGZv2qzZxfmP3H89Di47mDcc2Ob4/view?usp=sharing

Hi All

I have attached a sample pbix file on Google Drive. The file contains the query with any confidential data removed. The data includes "ID", "Placement Grouping", "Finance Start Date", "Finance End Date".

With that in mind, I want to create a unique reference number for each episode of care, which I am defining as having the same ID number, same placement grouping and consecutive dates across rows, without any break in dates. So for example, ID number 4636 has 5 seperate rows making up the single episode, so I'd like perhaps "Res4636-1" to show on all rows forming that episode with a column called "Episode ID".

[lbendlin]

Your sample data is inconclusive. All IDs have contiguous date intervals meaning each has a single episode.

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.