Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to 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.
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.
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/
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |