Forum Discussion
If Else Date Question
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.
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_)
4 Replies
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]) )
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.
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_)You are a star, thank you!
