Calculation of TIME

[AntBI26]

Hello all, 

Quite new to BI. 

I have a table containing a column of the 'Training time' of several tasks, and another column containing its respective 'Admin time'. The format of both column is set as TIME (HH:MM:SS).

To put it in contect, I want to have three visual cards: Overall Time, Training Time, and Admin Time. I have managed to sort the Training Time and Admin time one, but can't seem to manage doing the sum of these two.

My question is how do I add the Training Time and Admin time columns in Power Query to get an overall time ? 

Or 

How to add the two measures I created respectively for the total sum of the training time and admin time. 

For the sum of either Training time or the Admin time, my dax measure is below; 

Overall training time =

VAR TotalSeconds=SUMX('TrainingLog',HOUR('TrainingLog'[Training time])*3600+MINUTE('TrainingLog'[Training time])*60+SECOND('TrainingLog'[Training time]))

VAR Days =TRUNC(TotalSeconds/3600/24)

VAR Hors = TRUNC((TotalSeconds-Days*3600*24)/3600)

VAR Mins =TRUNC(MOD(TotalSeconds,3600)/60)

VAR Secs = MOD(TotalSeconds,60)

return IF((Hors + (Days*24))<10,"0"&(Hors + (Days*24)),(Hors + (Days*24)))&":"&IF(Mins<10,"0"&Mins,Mins)&":"&IF(Secs<10,"0"&Secs,Secs)

[amolrs · Accepted solution]

@AntBI26 , Consider changing the data types of the 'Training Time' and 'Admin Time' columns (or duplicates of those) to Decimal in Power Query. 'Total Time' can then be added as a new column by adding the first two.

You may also want to refer the solution given in this post:

Solved: Problems with hours greater than 24 hours - Microsoft Fabric Community

Cheers,

Amol

[AntBI26]

Many thanks for your help @amolrs, it works. 

I have attached the process I did below just in case it is relevant to someone else. 

Best wishes

[AntBI26]

Thanks @tharunkumarRTK for responding. I have tried it , but it seems I am getting an Errror (Check photo 1)

I wonder if it because my columns (Training time and Admin time) are set as 'Time' data types ?

I have changed the data types as to your example, but then it seems the calculation is wrong (hours) is concerned (see photo 2):

[tharunkumarRTK]

@AntBI26 

Assuming your requirement is to add two durations (Admin Time and Training Time). In power query you can achieve it using the below formula. Paste this formula in the custom column window

Text.Combine(List.Transform(Table.AddColumn(Table.FromColumns(
{List.Transform(Text.Split([Training Time], ":"), each Number.From(_)),
List.Transform(Text.Split([Admin Time], ":"), each Number.From(_))}, {"Training Time", "Admin Time"}), "Addition", each [Training Time] + [Admin Time])[Addition], each Text.PadEnd (Text.From( _ ),2,"0")), ":")

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun