topic Re: Calculating Working Hours calculate from Time Colums in DAX Commands and Tips

Calculating Working Hours calculate from Time Colums

Allen01 — Sat, 29 Oct 2022 10:19:36 GMT

Hi, I'm new in power bi

I'm trying to calcualate engineer total working hours from End Time - Start Time.

When I use in query in Excel I can use End Time - Strat Time with substract (Duration). When I present chart format I just need to add Total Working Hrs Cloums to Value field and my sum time is display.

But when I use in power bi duration data is not show as [hh]:mm format.

I would like to get idea

1) Duration to [hh]:mm Format (That can sum in value field)

2) End Time - Start Time = Time Format ( That can sum in value field)

I would like to show as below chart table (Total support Ticket Number & Total working Hours)

query chart

I already try from below link but not yet work for me. Please kindly help me for some solution.

https://www.mssqltips.com/sqlservertip/7061/calculate-date-difference-power-bi-dax-datediff/

https://powerusers.microsoft.com/t5/Power-Query/Converting-Decimal-Hour-to-HH-MM-Format/m-p/1377637#M63206

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362#M109 (use dayhoursminute)

Re: Calculating Working Hours calculate from Time Colums

Anonymous — Sat, 29 Oct 2022 11:34:43 GMT

Hello could you please try

Total working hour = format([end time] - [start time],"hh:mm:ss")

and to find the sum use:

sum of time = FORMAT(SUMX(Allen,Allen[End Time] - Allen[Start Time]),"HH:MM:SS")

Re: Calculating Working Hours calculate from Time Colums

Allen01 — Mon, 31 Oct 2022 06:29:32 GMT

Anonymous

Thank you for your solution. But when I try

(1) Total working hour = format([end time] - [start time],"hh:mm:ss")

it's show the time that I want but when i use this column in the report values field it's count not show as sum.

When i add new column and use this formula

(2) sum of time = FORMAT(SUMX(Allen,Allen[End Time] - Allen[Start Time]),"HH:MM:SS")

it's show diffrent time then actual working hours.

Maybe I'm doing wrong. Can you explain to me in the power bi i need to use new column for (1) and (2) or (2) is measure of cloumn (1) ?

Thank you for your help.