Forum Discussion
Calculate difference between two date/time values
Hi everyone,
I try to calculate the difference, in terms of time, between two datetime values. The problem is that when I use datediff, there is an error message : "In DATEDIFF function, the start date cannot be greater than the end date". This message isn't justify because the start date isn't greater than the end date...
I tried also a "simple" difference between the two dates (with DATE, RIGHT and LEFT) but it doesn't work because of the format.
An exemple of date : 2015-08-12T08:14:03.2830000.
Is there anyone that could help me please?
Sorry if the question seems basic, I'm not (yet) an expert in DAX :-)!
Thank you! Have a nice day!
Yeah, I would also try to look for some inconsistencies in DB. I hope you will find it! :smileywink:
You are welcome Anabelle.
Regards.
Pavel
10 Replies
Hi CGanabelle. I use DATEDIFF function and it works right.
I just used your example of date valu, then I created another one date value by adding one month and used DATEDIFF function.
Result is like this:
DAX: Diff = DATEDIFF(Table1[Date1];Table1[Date2];DAY)
Syntax for the function is:
DATEDIFF(<start_date>, <end_date>, <interval>
Maybe you just switched the two date columns (parameters) in the function.
Regards.
Pavel
Thank you very much for you answer! I've just checked but the error still appear unfortunatly even when I change the format.. I'm going to check if there isn't some inconsistencies in the database.
Thank you again to have tried to help me :)!
Have a nice day,
Regards,
AnabelleThere will be a lot of work if your database have massive data.
In addition to other posts, you can also use the following formula to calculate the difference between two datetime values even though there are in inconsistencies in the database.Diff = IF(Table1[Column1]<Table1[Column2],DATEDIFF(Table1[Column1],Table1[Column2],DAY),(-1)*DATEDIFF(Table1[Column2],Table1[Column1],DAY))
As display in screenshot, you will get the normal difference if the Column2 date is larger than Column1, otherwise it returns a negative number.
If you have any more questions, please don’t hesitate to ask.
Best Regards,
Angelia
Hi Angelia,
Thank you for your fournished answer! I found today the problem source! It was inconsistencies in the DB unfortunatly...Again thank you for your help you both!
As a novice, I'm happy to see so much people ready to help me in this forum!
Have a nice day!
Regards,
AnabelleCGanabelle I am very glad to hear you find the problem.
Best Regards,
AngeliaHi CGanabelle, awesome! Good to hear that you found the problem and that you have your expected result :smileyhappy:
Regards
Pavel
How do i get the difference between two fruits from the previous day, see table below:
Current state:Date Fruit Quantity
20/09/21 Apple 20
20/09/21 Orange 13
20/09/21 Grapes 30
21/09/21 Apple 18
21/09/21 Orange 9
21/09/21 Grapes 12
22/09/21 Apple 33
22/09/21 Orange 25
22/09/21 Grape 40
Desired State:
Date Fruit Quantity
Previous Quantity
Difference
20/09/21 Apple 20
20
20/09/21 Orange 13
13
20/09/21 Grapes 30
30
21/09/21 Apple 18
20
-2
21/09/21 Orange 9
13
-4
21/09/21 Grapes 12
30
-18
22/09/21 Apple 33
18
12
22/09/21 Orange 25
9
16
22/09/21 Grape 40
12
28
Is there a DAX formula for calculating the elapsed hours between two date time values?
