Duration for more than 24h with DAX

[Anonymous]

Hey guys.

i have the straight distance between two coordinates and i'd like to calculate, with a average speed of 80km/h, the travel time between them.

the problem is that some routes take more than 24h to be completed and then i stumble on the 'hour' formatting, that keeps me from getting the 'duration' correctly.

for example:

my current travel time formula: travel = TABLE[distance]/80/24

so, 2000(distance)/80(avg km/h)/24(turn into a 'valid decimal' to convert to datetime) returns 1,041666 (one day and one hour), but only show 01:00:00 (should be 25:00:00).

how can i proceed with that?

IMPORTANT! I need that only with DAX, sadly.

 

[v-piga-msft · Accepted solution]

Hi @Anonymous,

For your scenario, I suggest you could calculate the seconds first and then translate the seconds to HH:MM:SS.

seconds = 'Table1'[Distance]/80*3600

To translate the seconds to HH:MM:SS, you could refer to this blog Aggregating Duration/Time.

Here is my test result.

Best  Regards,

Cherry

[Anonymous]

Thank you sooooooooooo much, i was struggling to get this done.