New End date column based on start date

[Anonymous]

Hi all,

I want to add a column that containt the end date from the folliwng data.

Code	Phase	Date start
1122	Request	10-03-21 11:00
1122	Accepted	10-04-21 12:15
1122	Planned	12-04-21 13:21
5588	Request	25-03-21 18:13
5588	Accepted	30-03-21 14:22
9977	Request	4-04-21 9:12
9977	Accepted	8-04-21 17:18
9977	Planned	9-04-21 15:30
9977	Go	5-05-21 18:00

The end date is de date from the previous phase. so for for example my end result looks like below.

Code	Phase	Date start	Date End
9977	Request	4-04-21 9:12	8-04-21 17:18
9977	Accepted	8-04-21 17:18	9-04-21 15:30
9977	Planned	9-04-21 15:30	5-05-21 18:00
9977	Go	5-05-21 18:00

The "Date End" Is the "Date Start" value from the next phase. 

I Tried this with Dax but I only get the datediff from the last date. I don't know how to do this in M language for each code row.

It needs to be in Power Query so I can add the column to my chart. 

Yours faithfully,

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

Please check my attachment.

1.Refer to this article to get a ranking by category. And copy the original table to get the second table, the first table sorting starts from 2, and the second table sorting starts from 1.

2.Then merge two tables.

3.Expand the table

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jihwan_Kim]

Hi, @Anonymous 

Please try to write the below calculated measure.

Date End =
VAR currentcode =
MAX ( Data[Code] )
VAR currentstartdate =
MAX ( Data[Date Start] )
VAR _firstnonblankvalue =
CALCULATE (
FIRSTNONBLANKVALUE ( Data[Date Start], SELECTEDVALUE ( Data[Date Start] ) ),
FILTER (
ALL ( Data ),
Data[Code] = currentcode
&& Data[Date Start] > currentstartdate
)
)
RETURN
COALESCE ( _firstnonblankvalue, "" )

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

[Anonymous]

Hi Ihwan,

Thank you for your reply. Unfortunately 

Your solutions works in dax But I want to make this in Powet Query, because then I have a column which I can add on my gantt. The gannt chart doesnt accept the dax formule