date difference calculation with positive and negative number

[Anonymous]

Hi All,

Currently i need to calculate 2 dates difference with Postive & negative numbers.

i need to have both positive and Negative numbers in calculation. when i use Datediff formula, i always have positive numbers. 

how to get negative numbers in date difference calculation. 

Example: 

Current date: 1/22/2019 

Start date: 12/31/2018 

Then value should be 22 and if

Current date: 1/22/2019 

Start Date: 2/22/2019

Then value shoule be -30.

[Anonymous]

I still get all positive numbers, I can't figure out why this won't work.

[v-frfei-msft · Accepted solution]

Hi @Anonymous,

To create a calculated column as below.

datediff = var diff = DATEDIFF('Table1'[Start date],'Table1'[Current date],DAY)
var diff2 = DATEDIFF('Table1'[Current date],Table1[Start date],DAY)
return
IF(Table1[Start date]<='Table1'[Current date],diff,0-diff2)

For more details, please check the pbix as attached.

Regards,

Frank

[Matthew_Theis]

Hello,

Wondering if we need two variable declarations for this purpose.  Datediff provides the difference between the two dates defined - it doens't matter the order in the function.  The code below worked just fine for me.

Regards,

Matthew

[v-frfei-msft]

Hi @Anonymous,

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

Regards,
Frank

[Stachu]

try just subtracting them, e.g.

Table[Current date] - Table[Start date]

you may need to format it as a whole number, and possibly round if you have times included (they're represented as fractions), but other than that should work fine

[Anonymous]

I have tried that, but that is not working.