Forum Discussion

CGanabelle's avatar
CGanabelle
Frequent Visitor
9 years ago
Solved

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 d...
Need Help
  • PavelR's avatar
    PavelR
    9 years ago

    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

PavelR's avatar
PavelR
Solution Specialist
9 years ago

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

 

 

CGanabelle's avatar
CGanabelle
Frequent Visitor
9 years ago

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

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

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



    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

    • Anmolgan's avatar
      Anmolgan
      Post Prodigy
      7 years ago

      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's avatar
    PavelR
    Solution Specialist
    9 years ago

    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