Simple calculated Date field using IF is not working...

[VFXPro]

 I'm simply trying to get a date value calculated using a column depending on a condition, but it doesnt' work, what I am missing?

Calculated Due Date:  If the contract is digital then use the (enddate column + 7 days) otherwise use (receiveddate column + 7)

"Table1"

Received Date   EndDate        Format    Due Date

01/01/2000       01/07/2000   Digital      ???????

(FORMULA THAT IS NOT RETU?rn)

DueDate = IF(Table1[Format]="Digital",DATEADD(Table1[EndDate],7,DAY),DATEADD(Table1[ReceivedDate],7,DAY))

Thanks in advance

[HotChilli]

DATEADD is an unusual function.  If the date you end up with doesn't exist in the table already, it shows as blank.

I've never looked in detail at this but I presume it uses the in-built pseudo dates (hidden) table, and if it can't find it, it shows blank.

2 solutions: 1 - just add (+) the number (7 in your case) to the date

           or     2 - use DATEADD but develop your own 'dates' table in the model

[Phil_Seamark]

Hi @VFXPro

Give this a go, I think it's close.  I have attached a PBIX file

DueDate = 
    IF(
        Table1[Format]="Digital",
        Table1[EndDate]+7,
        Table1[ReceivedDate]+7
        )

[VFXPro]

Thanks Phil!!   Your file works fine.  It look like you are creating this new calculated column directly from the table editor, where I was using the Query editor (via Edit Query).  When I do use the query editor, with your formula (or mine) I get the followin error...

Expression.Error: The name 'IF' wasn't recognized.  Make sure it's spelled correctly.

Is there a guidelines that tells the user when to create a new column in the data tab vs the use the "Edit Query" that opens a separate editor?

[Phil_Seamark · Accepted solution]

Hi @VFXPro

If you are using the Power Query Editor, then you can add a custom column using this formula.  For something like this it doesn't really matter if you create it in M (Power Query) or DAX (Power BI Desktop)

if [Format] = "Digital" 
then Date.AddDays([ReceivedDate],7) 
else Date.AddDays([EndDate],7)

[VFXPro]

Thanks @Phil_Seamark

This is very valuable guidance...  Thank you very much for your kindness and sharing your knowledge.