Set the current phase in a process

[sixelapowerbi]

Hello everyone,

I need help creating a new column in Power Query that would define the "Current Phase" of each asset in my process.

Here is an example.

ID	PhaseNumber	Start	End	ActivePhase	CurrentPhase
001	1	DD.MM.YYYY	DD.MM.YYYY	No	No
001	2	DD.MM.YYYY	*TODAY*	Yes	Yes
001	3	DD.MM.YYYY	*TODAY*	Yes	No
001	4	DD.MM.YYYY	DD.MM.YYYY	No	No
002	1	DD.MM.YYYY	DD.MM.YYYY	No	No
002	2	DD.MM.YYYY	DD.MM.YYYY	No	No
002	3	DD.MM.YYYY	*TODAY*	Yes	Yes
002	4	DD.MM.YYYY	*TODAY*	Yes	No
003	1	DD.MM.YYYY	DD.MM.YYYY	No	No
003	2	DD.MM.YYYY	DD.MM.YYYY	No	No
003	3	DD.MM.YYYY	DD.MM.YYYY	No	No
003	4	DD.MM.YYYY	DD.MM.YYYY	No	No
004	1	DD.MM.YYYY	DD.MM.YYYY	No	No
004	2	DD.MM.YYYY	DD.MM.YYYY	No	No
004	3	DD.MM.YYYY	*TODAY*	Yes	Yes
004	4	DD.MM.YYYY	DD.MM.YYYY	No	No

Currently my "ActivePhase" column looks if the "End" date is *TODAY*, so "Yes", if not "No".
I can have several active phases but I can only have at most one "CurrentPhase".

So I would like my "Current Phase" column to take the active phase which has the smallest 'PhaseNumber' for each ID.

Do you have any ideas please.

[sixelapowerbi]

It's works.

Thanks for the quick answer.

[serpiva64 · Accepted solution]

Hi,

to obtain this

- add a Today column

Date.From( DateTimeZone.FixedUtcNow())

- determine ActivePhase with a conditional column

- filtered rows

- grouped filtered table

- from a previous step i get unfiltered table

- merge unfiltered with grouped

= Table.NestedJoin(Custom1, {"ID"}, MinStart, {"ID"}, "Custom1", JoinKind.LeftOuter)

(you can merge the unfiltered with itself then change the second table)

- expanded 

- then add a conditional column for CurrentPhase

If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution!