Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
AntBI26
Frequent Visitor

Calculation of TIME

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)

 

 

1 ACCEPTED SOLUTION
amolrs
Frequent Visitor

@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

View solution in original post

4 REPLIES 4
amolrs
Frequent Visitor

@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

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 wishesBI solution - Time.jpg

 

AntBI26
Frequent Visitor

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):Trial Bi time1.jpg

 

tharunkumarRTK
Super User
Super User

@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")), ":")

 

tharunkumarRTK_0-1710759678536.png


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



Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.