cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tulio_DL Regular Visitor
Regular Visitor

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

 

Sem título.jpg

 

 

 

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tulio_DL Regular Visitor
Regular Visitor

Re: Help needed!! Data modeling: Time

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!

4 REPLIES 4
Moderator v-yuezhe-msft
Moderator

Re: Help needed!! Data modeling: Time

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Tulio_DL Regular Visitor
Regular Visitor

Re: Help needed!! Data modeling: Time

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

Re: Help needed!! Data modeling: Time

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 Regular Visitor
Regular Visitor

Re: Help needed!! Data modeling: Time

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!