If Else Date Question

[CallumJ]

Hi All,

Ratting around in my brain to see how this would work, here is my data:

No.	Stage	Date
1	P	10/05/2022
1	F	12/08/2022
1	A
2	P
2	F	15/06/2022
2	A	22/08/2022
3	P
3	F	20/08/2022
3	A
4	P	25/06/2022
4	F	25/04/2022
4	A	15/06/2022

P, F & A are Planned, Forecast & Actual. I am creating a new table (Duplicated query and grouped columns) for this data which amalgamates it, part of the requirement is that I must populate the date column in a certain way - If there is an Actual Date, use that, else use Forecast Date, Else use Planned Date.

New Table:

No.	Date
1	12/08/2022
2	22/08/2022
3	20/08/2022
4	15/06/2022

I'm a bit lost as to how to reference the Stage column while using those If & Else conditions. Any help would be greatly appreciated.

[Tahreem24 · Accepted solution]

@CallumJ Create a Column like this:

Column =

VAR A_ = CALCULATE(MAX(StageTable[Date]),StageTable[Stage]="A",ALLEXCEPT(StageTable,StageTable[No.]))

VAR P_ = CALCULATE(MAX(StageTable[Date]),StageTable[Stage]="P",ALLEXCEPT(StageTable,StageTable[No.]))

VAR F_ = CALCULATE(MAX(StageTable[Date]),StageTable[Stage]="F",ALLEXCEPT(StageTable,StageTable[No.]))

RETURN SWITCH(TRUE(),

A_=BLANK(),F_,

P_=BLANK(),A_,

F_=BLANK(),A_,

A_

)

[CallumJ]

You are a star, thank you!

[amitchandak]

@CallumJ , Based on what I got

A new calculated table in dax  =

Summarize(Filter(Table, not(isblank(Table[Date])) , Table[No], "Date", Max(Table[Date]) )

[CallumJ]

Hi Amitchandak,

Appreciate your response, however I need an order of preference for the Dates, i.e. if A ISNOTNULL() then use the A Date, else if F ISNOTNULL() then use F Date, else if P ISNOTNULL() then use 'Null'.

The distinction is that it doesn't matter what date is the Max/Min, I need to set up an order of prefence.