How to get the next date from another column using IF / OR statement

[JPSingh]

I am trying to build a formula which basically tells me what the next date. e.g the one highlighted below what I want is to show the date from engage to lost as this was the next date. I need to do this for each stage so for Diagnose it needs to show me which stage date it went into next and the same for Engage, prescribe and etc. I have tried to do this in Power Query but the formula looks right however, I need it to read or so example below. The next engage date should show if prescribe is blank then select propose date, if propose date is blank then conclusion, if that is blank then lost date.

This is the formula I used in power query but because I don't have or it just picks up the prescribe date if engage is blank but if prescribe is blank it doesn't pick up propose. Is this possible to do?

= Table.AddColumn(#"Expanded Lost (Createddate)", "Diagnose Date to Next Step Date", each if [Engage Created Date] = null then [Prescribe Created Date] else if [Prescribe Created Date] = null then [Propose Created Date] else if [Propose Created Date] = null then [Conclusion Created Date] else if [Conclusion Created Date] = null then [#"Won (Createddate).Created Date"] else if [Conclusion Created Date] = null then [#"Lost (Createddate).Created Date"] else null)

[JPSingh · Accepted solution]

Thank you. This works works and is exactly what I needed. Thanks alot

[bhanu_gautam]

@JPSingh , Try using updated formula

m
= Table.AddColumn(#"Expanded Lost (Createddate)", "Diagnose Date to Next Step Date", each
if [Engage Created Date] <> null then [Engage Created Date]
else if [Prescribe Created Date] <> null then [Prescribe Created Date]
else if [Propose Created Date] <> null then [Propose Created Date]
else if [Conclusion Created Date] <> null then [Conclusion Created Date]
else if [#"Won (Createddate).Created Date"] <> null then [#"Won (Createddate).Created Date"]
else if [#"Lost (Createddate).Created Date"] <> null then [#"Lost (Createddate).Created Date"]
else null)