cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sandokpn
Advocate I
Advocate I

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
v-frfei-msft
Community Support
Community Support

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 others find it more quickly.

View solution in original post

5 REPLIES 5
v-frfei-msft
Community Support
Community Support

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 others find it more quickly.

View solution in original post

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.

 

Matthew_Theis_0-1623909497161.png

Regards,

Matthew

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 others find it more quickly.
Stachu
Community Champion
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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

I have tried that, but that is not working. 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors