Creating End Date base on Next records Start Date - DAX

Anonymous ,

According to your description, my understanding is that you want to create a new column “End” from the next rows Start Date group by Case No.

In this scenario, we can create an index column and then get the next row data based on the index. Please refer to the following steps:

1. Open Power Query Editor, then click the “Group By” button, then select “Group by” as Case No, Operation as All Rows. This step will group the data by Case No.

2. Then edit the following query as the below one, this step will create index for each group.

= Table.Group(#"Changed Type", {"Case No"}, {{"Count", each Table.AddIndexColumn(_,"Index",1,1), type table}})

3. After that, click on the button on the right of Count. Expand the table.

We will get a table like below, we can change the name of these columns.

4. Close & Apply these changes . Then change the data type of Index to Decimal Number, the data type of Start to Date/Time.

5. Then we can create a calculated column using the following query:

Old = IF(CALCULATE(MIN(Table1[Index])) = MAX(Table1[Index]),NOW(), CALCULATE(MIN(Table1[Start]),FILTER(Table1,Table1[Case No] = EARLIER(Table1[Case No]) && Table1[Index] = EARLIER(Table1[Index])+1)))

6. The result will like below:

Please refer to the attached pbix file.

Regards,