cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
sreedharvengala Frequent Visitor
Frequent Visitor

Creating End Date base on Next records Start Date - DAX

Hi 

 

I want to create as Column for each row as End Date from the next rows Start Date grouped by Case No.  

 

Bit new to DAX, how can this be achievied?

 

"End" is the column i require.

 

Sample Date below:

 

Case NoOld ValueNew ValueStart End
02386-X7F7S7(no value)Submitted17/09/2018 9:1618/09/2018 9:00
02386-X7F7S7SubmittedAutomated Assessment18/09/2018 9:0018/09/2018 9:02
02386-X7F7S7Automated AssessmentCollection18/09/2018 9:0218/09/2018 9:02
02386-X7F7S7CollectionCollection18/09/2018 9:0218/09/2018 9:06
02386-X7F7S7CollectionPlanned for collection18/09/2018 9:0621/09/2018 13:11
02386-X7F7S7Planned for collectionSample received21/09/2018 13:1121/09/2018 13:50
02386-X7F7S7Sample receivedSample received21/09/2018 13:5021/09/2018 14:32
02386-X7F7S7Sample receivedNegative confirmed21/09/2018 14:3221/09/2018 14:32
02386-X7F7S7Negative confirmedNegative21/09/2018 14:32Current DateTime

 

so far upto

 

EndDate =
VAR curr = Table1[Start ]
VAR currCase = Table1[Case No]
RETURN
    MINX (
        FILTER ( Table1, Table1[Case No] = currCase && Table1[Start ] > curr ),
        Table1[Start ]
    )

But the Output does looks what I need... 

 

 

output.JPG

 

Issue is when subsequent date is equal to the current Start.

 

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Creating End Date base on Next records Start Date - DAX


@sreedharvengala ,

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.
1.png

 

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}})
2.png

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

 

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

 

 

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:
6.png

 

Please refer to the attached pbix file.

Regards,

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Moderator v-yuezhe-msft
Moderator

Re: Creating End Date base on Next records Start Date - DAX


@sreedharvengala ,

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.
1.png

 

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}})
2.png

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

 

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

 

 

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:
6.png

 

Please refer to the attached pbix file.

Regards,

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.