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
CGanabelle
Frequent Visitor

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!

1 ACCEPTED SOLUTION
PavelR
Solution Specialist
Solution Specialist

Yeah, I would also try to look for some inconsistencies in DB. I hope you will find it! Smiley Wink

 

You are welcome Anabelle.

Regards.

Pavel

View solution in original post

10 REPLIES 10
kkane
Regular Visitor

Is there a DAX formula for calculating the elapsed hours between two date time values?

sahal_BI
New Member

How do i get the difference between two fruits from the previous day, see table below:
Current state:

DateFruit

Quantity

20/09/21Apple

20

20/09/21Orange

13

20/09/21Grapes

30

21/09/21Apple

18

21/09/21Orange

9

21/09/21Grapes

12

22/09/21Apple

33

22/09/21Orange

25

22/09/21Grape

40

 

Desired State:

DateFruit

Quantity

Previous Quantity

Difference

20/09/21Apple

20

 

20

20/09/21Orange

13

 

13

20/09/21Grapes

30

 

30

21/09/21Apple

18

20

-2

21/09/21Orange

9

13

-4

21/09/21Grapes

12

30

-18

22/09/21Apple

33

18

12

22/09/21Orange

25

9

16

22/09/21Grape

40

12

28

CGanabelle
Frequent Visitor

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,
Anabelle

PavelR
Solution Specialist
Solution Specialist

Hi @CGanabelle, awesome! Good to hear that you found the problem and that you have your expected result Smiley Happy

 

Regards

Pavel

@CGanabelle I am very glad to hear you find the problem.

Best Regards,
Angelia

PavelR
Solution Specialist
Solution Specialist

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:

Výstřižek.PNG

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,
Anabelle

@CGanabelle

 

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

11.png

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

I have 2 dates one is stored inside my date and for other date I am using calculated column in order to store the end date into that, how an I calculate the difference in time period between those dates, I need the date period between all those dates is that possible with DAX?

How can I use calculated column inside my DAX and also I dont have a calender table inside my database.

2019-05-31 and end date is 2019-06-03 then the difference will give me 3 dates that is 2019-05-31,2019-06-01 2019-06-02 and 2019-06-03, in seprate rows.

PavelR
Solution Specialist
Solution Specialist

Yeah, I would also try to look for some inconsistencies in DB. I hope you will find it! Smiley Wink

 

You are welcome Anabelle.

Regards.

Pavel

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.