DAX conditional formula with dates in Power BI

Question

I have several columns with dates (formatted as dates in PBI modelling). Each column represents the stage ('Start' - 'Finish') I need a new column which shows at what stage each row is at.

So far I have this:

Procurement Stage = IF(Milestones[Close Project]<>"1/01/1900","Close 
Project",IF(Milestones[Enable The Contract]<>"1/01/1900","Enable The 
Contract",IF(Milestones[Award Contract]<>"1/01/1900","Award 
Contract",IF(Milestones[Recommend Offer]<>"1/01/1900","Recommend 
Offer",IF(Milestones[Evaluate Offers]<>"1/01/1900","Evaluate 
Offers",IF(Milestones[Implement Strategy]<>"1/01/1900","Implement 
Strategy",If(Milestones[Strategy Approval]<>"1/01/1900","Strategy 
Approval",IF(Milestones[Conduct Analysis]<>"1/01/1900","Conduct 
Analysis",IF(Milestones[Initiate Project]<>"1/01/1900","Initiate Project","Not 
Yet Started")))))))))

It comes with the following error:

DAX comparison operations do not support comparing values of type Date with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

I have double checked and ensured that all the columns are in the same format - date.

Can anyone help with this?

I need this done in Modeling, thus I need this in DAX.

Thanks.

Evgeny

The error is not about your columns. The literal "1/01/1900" is of type text and it can't be compared directly with date type columns. — Andrey Nikolov
@AndreyNikolov Thanks. I have used DATE(1900,1,1) to convert "1/01/1900" to Date format. It worked — ECXHD

RADO RADO · Accepted Answer · 2019-01-08T00:32:16

Try:

Procurement Stage =
VAR My_Date = DATE ( 1900, 1, 1 )
RETURN
    SWITCH (
        TRUE(),
        Milestones[Close Project] <> My_Date, "Close Project",
        Milestones[Enable The Contract] <> My_Date, "Enable The Contract",
        Milestones[Award Contract] <> My_Date, "Award Contract",
        Milestones[Recommend Offer] <> My_Date, "Recommend Offer",
        Milestones[Evaluate Offers] <> My_Date, "Evaluate Offer",
        Milestones[Implement Strategy] <> My_Date, "Implement Strategy",
        Milestones[Strategy Approval] <> My_Date, "Strategy Approval",
        Milestones[Conduct Analysis] <> My_Date, "Conduct Analysis",
        Milestones[Initiate Project] <> My_Date, "Initiate Project",
        "Not Yet Started"
    )

DAX conditional formula with dates in Power BI

1 Answers