Forum Discussion
New End date column based on start date
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,
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.
3 Replies
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
Super User
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.
