Incorrect output for IF function

[amlopez45]

Hi Everyone, 

I am not sure what is going on with the IF function but it is not working as I would expected to. I want to get the number of days between two dates if a certain condition is met. If the condition is not met I want it get me the number of days that have passed. 

This is my current formula. 

DateDiff = IF('Table'[Status] = "Closed", DATEDIFF('Table'[Return Date].[Date], 'Table' [Shipping Date].[Date], DAY), DATEDIFF('Table'[Return Date].[Date], TODAY(), DAY))

For example, when I add the DateDiff column that contains the formula above it outputs 4 for every row. 

Customer	Status	Return Date	Shipping Date	DateDiff
A	Closed	1/28/2019	2/1/2019	4
B	Active	2/4/2019		4
C	Active			4
D	Active	2/4/2019		4

How do I get it to output the correct value?

Note: When I take out the IF function and seperate the DATEDIFF functions the output is correct. 

[v-shex-msft · Accepted solution]

HI @amlopez45,

Can you please share pbix file for test? Your formula works well on my side.

Regards,
Xiaoxin Sheng

[amlopez45]

Hi @v-shex-msft,

The formula is working well now. I tried Collins method and it seemed to do the job. Not really sure what happened. Thank you for offering to help. 

[amlopez45]

Hi Everyone, 

I am not sure what is going on with the IF function but it is not working as I would expected to. I want to get the number of days between two dates if a certain condition is met. If the condition is not met I want it get me the number of days that have passed. 

This is my current formula. 

DateDiff = IF('Table'[Status] = "Closed", DATEDIFF('Table'[Return Date].[Date], 'Table' [Shipping Date].[Date], DAY), DATEDIFF('Table'[Return Date].[Date], TODAY(), DAY))

For example, when I add the DateDiff column that contains the formula above it outputs 4 for every row. 

Customer	Status	Return Date	Shipping Date	DateDiff
A	Closed	1/28/2019	2/1/2019	4
B	Active	2/4/2019		4
C	Active			4
D	Active	2/4/2019		4

How do I get it to output the correct value?

Note: When I take out the IF function and seperate the DATEDIFF functions the output is correct. 

[Anonymous]

hello @amlopez45

first things first, try changing your data format (top of modeling tab) to date for your calculated column and see if that fixes your issue

here is a picture of what I did.

best regards,

Collin

[amlopez45]

Hello @Anonymous,

Thank you for the suggestion, the formula is working well now. 

[Anonymous]

@amlopez45

Great to hear, be sure to mark the post as the solution for anyone else that comes along. 

best Regards,

Collin