cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PStavis
Frequent Visitor

How to work with duration correctly?

Hi!


I'm new to Power Bi, working on my first report. The goal is to create a graphic showing how many hours the train engineer waits between his check in and the train departs. The goal is 1h. And also be able to filter the information based on days/months/years.


Using PQ I created a calculated column ([DELTA_PERM) showing the difference between "partida" (date/hour of departure) and "ap mq" (date/hour of check in). I set this columns as "duration". From what I learned, the returned value (like 0.01:00:00) means 0 days + 1h + 0h minutes + 0 seconds. Another column I created, (Meta_Estadia) has the goal (1).


Then I moved to DAX and created a measure called "m_ESTADIA_1" to calculate the average time engineers wait using the following formula: (AVERAGE('CIC MESCLADO'[DELTA_PERM]))*24.


From there on I can't simply put everything together on my graphic. Either the goal shows up wrong on the graphic or I can't display the average time as hour (HH:MM).


Here's the pibx link: https://www.dropbox.com/s/gcw07yuh4e6xsjk/BI%20CIC%20v2.pbix?dl=0

And the data source: https://www.dropbox.com/s/w7lif6532z92x9i/Giro%20Locomotivas%20-%20Agosto%20V1.0.xlsm?dl=0

 

Maybe I should use serial data/time, calculate everything and then get back to the view I need? Appreciate any inputs.

Thanks!

1 ACCEPTED SOLUTION
aj1973
Community Champion
Community Champion

Check this out.

https://drive.google.com/file/d/1JyghX-9BuHN2dwQI5vH191ijs7JiFruC/view?usp=sharing

use tooltip to see the time in HH:MM

aj1973_0-1635192604540.png

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

View solution in original post

9 REPLIES 9
PStavis
Frequent Visitor

Hi Pat!

Thank you for your answer but unfortunately I couldn't figure out how to apply the contents of your post/video on my project.


First I split all dates/times into separate date and time. Then I created a custom column with a DAX formula:
Estadia = IF('CIC MESCLADO'[PARTIDA]=BLANK(), BLANK(), (FIXED( ('CIC MESCLADO'[PARTIDA]-'CIC MESCLADO'[AP MQ]) , 6)))
So it will calculate the difference between the values on "partida" and "ap mq". First I didnt use the if sentence and some values were calculated as negative (because there was nothing on "partida". Then I decided to play like excel (i use this kind of expression alot) so it will only calculate if both fields have data.

Then I tried a measure like you said in the video like this:
m_Estadia =
VAR rawresult =
SUMX('CIC MESCLADO','CIC MESCLADO'[DATA_S_PROG]-'CIC MESCLADO'[DATA_S_AP])
VAR result =
FIXED ( rawresult, 4 )
RETURN
result

But the result showed to me using a card was a negative value.

Long story short: Either i'm too dumb to understand this or i'm trying something very hard...

aj1973
Community Champion
Community Champion

Hi @PStavis

Nothing hard, you are just mixing up your calculations, it is like you are aggregating the sum of Potatoes from the sum tomatoes lol. 

The split of all Date/time columns in your models are better be done in Power Query, is this th case?

Can you resend the file with the new modifications! I wanna see how you split the columns and what result do you expect.

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

PStavis
Frequent Visitor

Heres the new file: https://www.dropbox.com/s/tedg29ovkf8gewl/BI%20CIC%20v2.2.pbix?dl=0


I don't see the point splitting all date/time columns then use a DAX function to join then again like Pat suggested: ( Activity[StopDate] + Activity[StopTime] ) – ( Activity[StartDate] + Activity[StartTime] )


What I need is simply calculate the differente between "partida" - "ap mq" columns and calculate the average time for a day, month, year and be able to filter it.


I found another suggestion on a video tutorial using the conversion to seconds elapsed, then you do the calculations using the seconds and finally format it as hh:mm. It kinda worked but theres a problem with rounding the underlying decimals. When I input some dates/times, instead of following exactly what I used (like 25/10/2021 13:00) it will appear as 25/10/2021 12:59 and those seconds end up mismatching alot when you sum up things.

aj1973
Community Champion
Community Champion

@PStavis 

It looks like in your case there would be no need to split. 

So are you interested to see Seconds in your Card? or Minutes? Or Hours?

I am trying to add a Column for Estadia in a different way then converted into what you need.

aj1973_0-1635184509723.png

 

aj1973_1-1635184611894.png

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

PStavis
Frequent Visitor

What I need is to build a graphic like this one:

PStavis_0-1635186234772.png

Its basically the average "estadia" (which is calculated from "partida" - "ap mq") for each day and theres a line showing the goal (01:00 hour).

 

I also plan to build more reports based on time difference, thats why I'm trying to understand it too.

PS: The image attached comes from a report sent via email, I dont have access to the original files to extract the data used there, thats why i'm build one on bi

aj1973
Community Champion
Community Champion

Check this out.

https://drive.google.com/file/d/1JyghX-9BuHN2dwQI5vH191ijs7JiFruC/view?usp=sharing

use tooltip to see the time in HH:MM

aj1973_0-1635192604540.png

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

View solution in original post

PStavis
Frequent Visitor

I did change a little bit your graphic so it shows correctly (the line "hour" is set as 1 while the bar in the graphic shows 60, i just divided the measure by 60).

PStavis_0-1635194879791.png

But your solution is very good, i'm studying what you did to replicate by myself. i'll set this one as solved.

 

One thing that still bothers me is the rounding on the data tab. Like this one:

PStavis_1-1635194967780.png

Even though the original data is 19/10/2021 20:00:00 due to some round up it looses one second on the data tab, and when you calculate stuff you end up with one more or one less second:

PStavis_2-1635195037796.png

Like this calculation that returns 121 seconds but my data doesnt have seconds (its supposed to return 120 seconds only).

I guess i have to live with this difference.

 

Thank you alot mr. Amine and mr Pat!

PStavis
Frequent Visitor

So I was able to find  way to correct those round issues I said earlier. I'm still studying a better way but so far what I did:

 

1) Using Power Query, create a new calculated column: Date/hour 1 - date/hour 2.

2) Transform the column into a Duration.TotalMinutes, I did it using the options on the upper bar (Duration -> Total Minutes).

3) Round up the column.


Now insted of showing 00:59:59.09985 for example, it rounds up to 60 minutes.

 

It's not a big issue if you're calculating the average of some hours but in big calculations, each lost minute will throw the result off for a couple of hours maybe.

 

Hope it helps anyone with similar issues!

mahoneypat
Super User
Super User

Please see this article.

Calculate and Format Durations in DAX – Hoosier BI

 

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.