Forum Discussion
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!
Check this out.
https://drive.google.com/file/d/1JyghX-9BuHN2dwQI5vH191ijs7JiFruC/view?usp=sharing
use tooltip to see the time in HH:MM
9 Replies
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
resultBut 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...
Hi Anonymous
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.
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.
