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

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: date difference calculation with positive and negative number

Hi @sandokpn,

 

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)

Capture.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Super User
Super User

Re: date difference calculation with positive and negative number

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

sandokpn Frequent Visitor
Frequent Visitor

Re: date difference calculation with positive and negative number

I have tried that, but that is not working. 

Community Support Team
Community Support Team

Re: date difference calculation with positive and negative number

Hi @sandokpn,

 

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)

Capture.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

Re: date difference calculation with positive and negative number

Hi @sandokpn,

 

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


Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.