Latest Enterprise DNA Initiatives

Time duration graphing wrong

Good Morning all

I’m running into an issue trying to display some data in a table.

I have an Excel Spreadsheet that has a column for time a truck was traveling in hours:minutes and is marked as Duration in Power Query. That works just fine.

When I try to include this duration column into my visuals it changes the column to a decimal number and doesn’t have any selections for marking the column as a time duration.

Thoughts on a fix for this??? We need to be able to visually look at the time a truck has been traveling.

Guy

I realize that I can format the column as Text and display the time traveled but Text won’t allow any measures to be used that would sum the column to see totals for the Month, Quarter, etc.

Hi @GuyJohnson. Perhaps you could keep the column as decimal and write a measure to format the time as text (or duplicate your column and keep one column as decimal for calculations and one column formatted as text for display)? If that won’t work, please upload your work-in-progress PBIX and a marked-up screenshot of the visual of interest, where your desired outcome is noted.
Greg

Hi @GuyJohnson,

Duration represents a length of time. It’s converted into a Decimal Number Type when loaded into the model. As a Decimal Number type it can be added or subtracted from a Date/Time field with correct results and you can easily use it in a visualizations that shows magnitude.

You can use the DAX function TIMEVALUE to convert a text time to time OR use the FORMAT function to convert a duration into a text time (explore options here).

If all eslse fails consider keeping two columns, one text for display and one numberic for aggregation.

I hope this is helpful.

1 Like

Thanx @Greg and @Melissa for the suggestions.

I like the idea of the two columns formatted differently - I never thought of that.

Thanx for the DAX suggestions - let me play with this and see what I can come up with.

Guy

1 Like

@Greg @Melissa

The FORMAT command seems to work and not work at the same time.

I run the following DAX measure - Test Time = FORMAT( 0, “dd:hh:mm:ss” ) and it returns the following value 30:00:00:00 I’m confused on where the entry for 30 days is coming from.

Thoughts??

Guy

Hi @GuyJohnson. Would need to see your data/PBIX to comment. Greg.

@Greg

See attached

Driver’s Scorecard.pbix (321.5 KB)

Guy

Hi @GuyJohnson. The FORMAT command formats the display of a datetime value; you were using a number.

Try something like:

Test Time 2 = FORMAT( DATE( 2021, 05, 06) + TIME( 17, 23, 42), "dd:hh:mm:ss" )

Hope that helps.
Greg
eDNA Forum - Driver’s Scorecard.pbix (308.6 KB)

1 Like

Thanx @Greg

I don’t think that answers my question, unless I’m missing the boat here.

I’m trying to get a total of the Time a driver is out on the road that can be sliceable. I believe your DAX formula will need work for a single input as in your example Test Time 2

Guy

Hi @GuyJohnson. I just generated a datetime value and used it as an example. The FORMAT function works on a Date or DateTime value, so apply it wherever you want. Good luck.
Greg

Thanx for the assist

Hi @GuyJohnson, did the response provided by @Greg help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

The responses from both @Greg and @Melissa were very informative but neither of them addressed the original problem I posted.

At this point I cannot mark anything as a solution but you Enterprise DNA feels they have to close this so be it.

Maybe this article can help https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/td-p/793639

I have solved the issue with a little help from @cmalone1112 and the link provided and a Google search leading to RADACAD

I created a new column using: Time in Minutes = HOUR( ‘Scorecard by Driver’[Total Road Time - Copy] )*60 + MINUTE( ‘Scorecard by Driver’[Total Road Time - Copy] )

This gives me my total traveled time in minutes which I summed as Total Driving Minutes

I then used the formula from RADACAD as follows

Total Driving Time =
var vMinues=[Total Driving Minutes]
var vHours=int( vMinues/60)
var vRemainingMinutes=MOD(vMinues, 60)
return
vHours&" Hours & “& vRemainingMinutes& " Minutes”

Thanx to @Greg @Melissa for input

The link to the RADACAD article is: Calculate Duration in Days Hours Minutes and Seconds Dynamically in Power BI using DAX - RADACAD - Hope it helps someone else in the same situation.

Guy