Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
I still get all positive numbers, I can't figure out why this won't work.
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
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
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
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
I have tried that, but that is not working.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |