Forum Discussion

VFXPro's avatar
VFXPro
Icon for Advocate II rankAdvocate II
7 years ago
Solved

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

 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

Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    7 years ago

    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)

     

     

5 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    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's avatar
      VFXPro
      Icon for Advocate II rankAdvocate II
      7 years ago

      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's avatar
        Phil_Seamark
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        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)

         

         

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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