DATEDIFF - show 1 instead of 0 if date is the same

[TIGER8855]

I'm using DATEDIFF shown below in a calculated column to compare dates in two different columns to show me the difference in days. For some rows however, the dates are the same and instead of showing a 0 in the calculated column, I need it to show 1. In addition, in some cases there will be no dates in either column. Currently in these instance, no value is returned in the calculated column. I want this to remain this way and not for a 1 to be returned as they are the same.

Date1-Date2 days = DATEDIFF('Table'[Date1],'Table'[Date2],DAY)

[TomMartens]

Hey @TIGER8855 ,

provide sample data and the expected results.

Regards,

Tom

[TIGER8855]

@TomMartens refer to table below:

Date1	Date2	Date1-Date2 days
28-Jul-19	9-Dec-20	499
	28-May-18
29-Jul-19	29-Jul-19	1
29-Oct-19
2-Oct-19	29-Oct-19	27
10-Oct-21	10-Oct-21	1
30-Nov-21	24-Jul-22	236

[Anonymous]

Okay, sorry for the reoccuring issue. Hard to do without the actual data, I am trying free hand here. Try this: 
Date1-Date2 days =if( DATEDIFF('Table'[Date1],'Table'[Date2],DAY) = 0 ,1,if(DATEDIFF('Table'[Date1],'Table'[Date2],DAY) = Blank(),Blank(),DATEDIFF('Table'[Date1],'Table'[Date2],DAY)))

[TIGER8855]

@Anonymous no good unfortunately. It's back to adding a 1 when both dates are blank.

[ReneMoawad]

Hello,

Date1-Date2 days =
Var output = DATEDIFF('Table'[Date1], 'Table'[Date2], DAY)
Return
IF(output = 0, 1, output)

[TIGER8855]

@ReneMoawad Blank Date1 & Date2 are returning a value of 1 with this one unfortunatley.

[ReneMoawad]

Try the below

Date1-Date2 days =
Var output = DATEDIFF('Table'[Date1], 'Table'[Date2], DAY)
Return
IF(

    'Table'[Date1] = BLANK()

    && 'Table'[Date2] && BLANK()

    , 0

    , IF(output = 0, 1, output)
)

[TIGER8855]

@ReneMoawad Thanks for your on-going help. The Blank Date1 & Date2 are still returning a value of 1 with this one. In a seperate post I have provided a table with sample data and the expected result for the Date1-Date2 days calculated column.

[Anonymous]

Hi,
Try this: 
Date1-Date2 days =if( DATEDIFF('Table'[Date1],'Table'[Date2],DAY) = 0,1,DATEDIFF('Table'[Date1],'Table'[Date2],DAY))

[TIGER8855]

@Anonymous I tested and it changes the 0 to 1 for the same dates however it also now adds a 1 when both dates are blank. Is there any way to exclude a value showing when both dates are blank?

[Anonymous]

Hi, might not be the best code I have written, but try this:
Date1-Date2 days =if( DATEDIFF('Table'[Date1],'Table'[Date2],DAY) = 0 && DATEDIFF('Table'[Date1],'Table'[Date2],DAY) <> Blank() ,1,DATEDIFF('Table'[Date1],'Table'[Date2],DAY))

[TIGER8855]

@Anonymous with this code, blank dates now return no values however for instances where the date is the same, it's returning a 0

[Anonymous · Accepted solution]

Hi, try this
1-2 =

Var output = DATEDIFF('Table-test'[Date1], 'Table-test'[Date2], DAY)

Return

IF(ISBLANK('Table-test'[Date1])||ISBLANK('Table-test'[Date2]),blank(),if(output = 0,1,output))

[TIGER8855]

Thanks @Anonymous ! This one works exactly as I expected. Thanks for your continued help to a solution.

Lesson for me next time to include sample data from the start.

[Anonymous]

Of course!
I think that will  be helpful as you run into future issues.
Cheers!