How to do sum of multiple time column(HH:MM:SS) in Power BI

[Anonymous]

Hi Everyone,

I am facing an issue where I am unable to do sum of multiple columns which is of format HH:MM:SS. Please see below:

I want to find the SUM of all the columns i.e Time to reach merchant+wait time at store+....

The result must be in a new column in HH:MM:SS format.

Is this possible?

Please help

Regards

RK

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

Please check the data types and set them to "time" type.

Then try the dax below:

sum_time = 'Table'[Wait time at store]+'Table'[Time to reach merchant]+'Table'[Time to reach customer location]

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you. It worked 🙂

[Greg_Deckler]

You will need to convert those to seconds, add them and essentially convert them back more or less. Look at the links below, they should get you what you need.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389

https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486

https://community.powerbi.com/t5/Quick-Measures-Gallery/Duration-to-Seconds-Converter/m-p/342279#M92

[Anonymous]

Hi @Greg_Deckler : I understood that but my question is how do I add these columns. Whats the formula? could you please share an easy example.

Thanks

[Anonymous]

Hi @Anonymous 

try this

Column = FORMAT(TIME(HOUR(Check[Column1]+Check[Column2]),MINUTE(Check[Column1]+Check[Column2]),SECOND(Check[Column1]+Check[Column2])),"hh:mm:ss")

@Anonymous  the above formula is in dax. WIll check for power Query.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[Anonymous]

Hi @Anonymous 

New custom column=#time(Time.Hour([Column1])+Time.Hour([Column2]),Time.Minute([Column1])+Time.Minute([Column2]),Time.Second([Column1])+Time.Second([Column2]))

Modify formula for 4 columns.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[Anonymous]

Thank you everyone for your responses and help 🙂