Forum Discussion

Tulio_DL's avatar
Tulio_DL
Helper I
9 years ago
Solved

Help needed!! Data modeling: Time

Greetings!   First of all, to everyone that contributes in any way to make this forum running and are solving doubts like mine everyday, thank you very much! I'm getting started on data modeling, D...
Need Help
  • Tulio_DL's avatar
    Tulio_DL
    9 years ago

    Hey guys! Thanks for repplying to my post... I've come to an acceptable way to do it with the formulas DATEDIFF (as mentioned above) and other minor ones.

     

    So if you find yourself struggling with the same doubt as the topic plz msg me in box that I can send you the step to step and explanations!

     

    Thank you Anonymous and vrs!

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi Tulio_DL,

I am not quite clear about your first and second questions. Do you mean that when you import date/time data from data source to Power BI Desktop, these data don’t display as you expected? If that is the case, please help to post the sample data in your original data source.

In addition, about the third question, Power BI Desktop can’t display 25:30:00 as time type, if you persist to show 25:30:00 instead of 1 day + 01:30:00 for a field in table, you would need to format the field as text type.


Thanks,
Lydia Zhang

  • Tulio_DL's avatar
    Tulio_DL
    Helper I
    9 years ago

    Hello Anonymous! 

     

    First of all thanks for replying on the topic and I'll try to explain better what I need. I'm sorry if my english is a little bad, I'll try to exemplify:

     

    Column1                          Column 2

    27/01/2017 10:30:00       30/01/2017 08:30:00

     

    I need to be able to extract the whole amount of time (preferably in "hh : mm : ss") passed between these two columns, wich is 70:00:00. You mentioned I'd need to format as text to do it, wich won't help me very much because I'll eventually need to use them on a calculation or something, so how do I extract 2 days 22:00:00 out of it?

     

    Thanks Lydia Zhang!

     

     

    • vrs's avatar
      vrs
      Frequent Visitor
      9 years ago

      Hello Tulio_DL

       

      Not sure about your need but I suggest you create two columns number of days and duration

       

      Column1Column 2NdaysDuration 
      27/01/2017 10:30 30/01/2017 08:25:23221:55:23

       

      Ndays= QUOTIENT(DATEDIFF(table[Column 1];table[Column 2];HOUR);24)

      Duration= table[Column 1]- table[Column 2]

       

      You can get duration in seconds DATEDIFF(table[Column 1];table[Column 2];SECOND), it can be useful if you need to compare different values.

       

      Hope this helps,

      • Tulio_DL's avatar
        Tulio_DL
        Helper I
        9 years ago

        Hey guys! Thanks for repplying to my post... I've come to an acceptable way to do it with the formulas DATEDIFF (as mentioned above) and other minor ones.

         

        So if you find yourself struggling with the same doubt as the topic plz msg me in box that I can send you the step to step and explanations!

         

        Thank you Anonymous and vrs!