Forum Discussion
Duration time divided per hour
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
Hi Anonymous ,
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.
4 Replies
Hi Anonymous ,
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.
Anonymous ,
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.
Dear Anonymous ,
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.
Hi Anonymous ,
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.