How to work with duration correctly?

[PStavis]

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!

[PStavis]

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]

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.

[PStavis]

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]

@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.

[PStavis]

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

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 · Accepted solution]

Check this out.

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

use tooltip to see the time in HH:MM

[PStavis]

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

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:

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:

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]

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]

Please see this article.

Calculate and Format Durations in DAX – Hoosier BI

Pat