Forum Discussion
date difference calculation with positive and negative number
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.
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
6 Replies
v-frfei-msftCommunity Support
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
- 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 
Matthew_TheisAdvocate II
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
- Stachu
Community Champion
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
