cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
VFXPro Frequent Visitor
Frequent Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Phil_Seamark Super Contributor
Super Contributor

Re: fRe: Simple calculated Date field using IF is not working...

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)

 

 

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

5 REPLIES 5
Phil_Seamark Super Contributor
Super Contributor

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

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
        )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

HotChilli New Contributor
New Contributor

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

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

VFXPro Frequent Visitor
Frequent Visitor

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

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 Super Contributor
Super Contributor

Re: fRe: Simple calculated Date field using IF is not working...

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)

 

 

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

VFXPro Frequent Visitor
Frequent Visitor

Re: fRe: Simple calculated Date field using IF is not working...

Thanks @Phil_Seamark

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

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 380 members 3,939 guests
Please welcome our newest community members: