Duration time divided per hour

[SanKing]

Dear Experts,
I was wondering if you could help me with a formula / visualization, that I would like to add in one of our reports.
We would like to show the BOT work time per hour, every day of the week.

I have a problem with processes that sometimes take several hours.
When I try to display SUM of duration, graph is taking full duration (these several hours) to starting time.

Could you advise me how to calculate them so that they are displayed correctly?
Thank you in advance!
Best regards

[v-kaiyue-msft · Accepted solution]

Hi @SanKing ,

Based on your description, I created this data.

1. Create a calculated column, starting and ending hours in the past.

Start_1 =
DATE(
YEAR('Table'[StartTime]),MONTH('Table'[StartTime]),DAY('Table'[StartTime]))+TIME(HOUR('Table'[StartTime]),0,0)

End_1 =
DATE(
YEAR('Table'[EndTime]),MONTH('Table'[EndTime]),DAY('Table'[EndTime]))+TIME(HOUR('Table'[EndTime]),0,0)

2. Create a calculation sheet to get every hour of every day.

Table 2 =
GENERATESERIES(
DATE(2024,1,3),DATE(2024,1,5),0.041666667)

3. Create a calculated column in the calculation table to get the hours between the start and end dates.

con1 =
var_min=
MAXX(FILTER(ALL('Table'),
'Table'[Start_1]=EARLIER('Table 2'[Value])),[Start_1])
var_max=
MAXX(FILTER(ALL('Table'),
'Table'[Start_1]=_min),[End_1])
return
CONCATENATEX(
FILTER(ALL('Table 2'),
'Table 2'[Value]>_min&&'Table 2'[Value]<_max),[Value],"-")

con2 =
CONCATENATEX(
'Table 2','Table 2'[con1],"-")

4. Create measure.

Test =
var_minstart=
DATE(
YEAR(MAX('Table 2'[Value])),MONTH(MAX('Table 2'[Value])),DAY(MAX('Table 2'[Value])))+TIME(HOUR(MAX(' Table 2'[Value]))+1,0,0)
var_maxend=
DATE(
YEAR(MAX('Table 2'[Value])),MONTH(MAX('Table 2'[Value])),DAY(MAX('Table 2'[Value])))+TIME(HOUR(MAX(' Table 2'[Value])),0,0)
var_min1=
MINX(FILTER(ALL('Table'),'Table'[Start_1]=MAX('Table 2'[Value])),[StartTime])
var_max1=
MINX(FILTER(ALL('Table'),'Table'[End_1]=MAX('Table 2'[Value])),[EndTime])
var _min1111=
MINX(FILTER(ALL('Table'),'Table'[Start_1]=MAX('Table 2'[Value])),[Start_1])
var _max1111=
MAXX(FILTER(ALL('Table'),'Table'[Start_1]=MAX('Table 2'[Value])),[End_1])
RETURN
SWITCH(
TRUE(),
MAX('Table 2'[Value]) in SELECTCOLUMNS(ALL('Table'),"end",'Table'[End_1]),
DATEDIFF(
_maxend,_max1,MINUTE),
MAX('Table 2'[Value]) in SELECTCOLUMNS(ALL('Table'),"start",'Table'[Start_1]),
DATEDIFF(
_min1,_minstart,MINUTE),
CONTAINSSTRING(MAX('Table 2'[con2]),MAX('Table 2'[Value]))=TRUE(),
60,
_min1111=_max1111,
DATEDIFF(
_min1,_max1,MINUTE)
)

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[SanKing]

Dear @v-kaiyue-msft ,

My question doesn't relate to performence issue.

On different example: we have drivers in our company, each driver has trips. One driver has a trip that starts On Monday at 6:00 AM and finishes at 6:30 -> we want to show what this driver at 6:00 had 0.5 hour trip. But at 1:00 PM the same driver stars trip that last till 5 PM -> we want to show that each hour from 1PM to 5 PM this driver was having the trip. We don't want to show 4 hours cumulated at 1 PM.

[SanKing]

@v-kaiyue-msft ,

Thank you for your reply.

No, it's not performance issues. We want to show load of BOT not related to MS / PBI.

I have data as I mentioned in the description, which can extend for several hours and I'm looking for a solution how I can show these in each hour.

E.g. Process6 started execution at 17:51 and was running for 12 hours 19 minutes, I would like to show this process in each hour: 17:00 -> 9 minutes, 18:00 -> 60 minutes, ..., 6:00 (next day) 10 minutes.

[v-kaiyue-msft]

Hi @SanKing ,

Are you encountering performance issues? You can optimize Power BI in terms of data sources, data models, visualizations, environments, etc. For detailed information, please refer to the document: Optimization guide for Power BI - Power BI | Microsoft Learn.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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