Help needed!! Data modeling: Time

[Tulio_DL]

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, DAX language and into this forum, so if my problem has already been solved elsewhere I appologize.

As told on the subject, I need help on modeling time data, on PowerBI I have something like this:

The column on the left represents the starting event and the column to the right it's end. I want to extract 2 informations out of it:

1. Is the raw time in "hh : mm : ss" (Spaces are to avoid the emoji) where the result has to be 15:01:44 and;

2. I want to consider a period of working hours on a day, like from 08:00:00 to 17:00:00 (9 hours), how can the data be modeled to extract the result 00:03:34?

3. Bonus question: On this sheet I'll have events that takes longer then 24 hours to complete, so is it able to model the result to be shown like 25:30:00 instead of 1 day + 01:30:00?

Thanks in advance!!

Diego

[v-yuezhe-msft]

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]

Hello @v-yuezhe-msft! 

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]

Hello @Tulio_DL

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

Column1	Column 2	Ndays	Duration
27/01/2017 10:30	30/01/2017 08:25:23	2	21: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 · Accepted solution]

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 @v-yuezhe-msft and @vrs!