Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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 @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)

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

6 REPLIES 6
bevabbs
New Member

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

 

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

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.

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 @Anonymous,

 

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 🙂

Anonymous
Not applicable

I have tried that, but that is not working. 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors