working with multiple dates, please help!

[jhenderson525]

Greeting,

I would like to create two additional columns to the table below:

1st column  {Current Phase Date] - would find the value of the [Current phase] of each [Project], then somehow look up the date in the relative phase date columns, and retrun that date.

For example Project A's current phase is 3, so return the date 1/1/2020 in this

2nd column [Next Phase Date]- similarly, would find the value of [Next phase] for each project, then return the date from the relative column.

is this possible?

Project	Current phase	Next Phase	Phase 1 date	Phase 2 date	Phase 3 date	Phase 4 date	Phase 5 date	Phase 6 date
A	3	4	1/1/2016	1/1/2017	1/1/2020	1/1/2024	1/1/2030	1/1/2032
B	5	6	1/1/2019	5/1/2019	7/1/2019	8/1/2019	1/1/2023	1/1/2024
C	Not started	1	1/1/2024	2/1/2024	5/1/2024	8/1/2024	10/1/2024	1/1/2025
D	6	Complete	1/1/2022	2/1/2022	3/1/2022	4/1/2022	5/1/2022	8/1/2022

[amitchandak]

@jhenderson525 , Not very clear , but you need a  new column like

next phase date= minx(filter(Table, [Project] = earlier([Project])  && [Current Phase] = earlier([Next Phase]) ), [Phase 1Date])

Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

[jhenderson525]

Thank you for responding. I hope this image will help provide some clarity. Basically i need to create two new columns in a new table that will reference the respective dates for the current phase and next phsae of each project. With the caveat that Current Phase for some projects may be blank (since they have not started) and Next Phase may also contain blanks (since they have completed). Thanks again!

[v-cgao-msft · Accepted solution]

Hi @jhenderson525 ,

If this is not a calculation table and does not contain calculated columns, you may consider doing this in the PowerQuery editor.

Make a copy of the table and name it Table2-->select [Phase 1 date] to [Phase 6 date] and unpivot the columns-->add 2 custom columns:

if Text.Contains([Attribute],[Current phase]) then [Value] else null

if Text.Contains([Attribute],[Next Phase]) then [Value] else null

select [project] and group data:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[jhenderson525]

@v-cgao-msft @amitchandak Thank you both for taking the time out of your day to respond. I really appreciate the assistance. The pivot table solution is perfect!