Forum Discussion
Split duration into hourly basis and also split dates when the process overflows the next day
Hi v-henryk-mstf ,
PFA screenshot with new start and end time columns which I added for your reference, basically I won't it to split into hourly duration and find run time within that hour.
So if you see for key x3 its start time is 2020-01-17T22:45:00 and end time is 2020-01-17T23:05:00 so I would want to split the data to show that the machine M2 has run for 15mins in the 22nd hour and for 5 mins in the 23rd hour, thats the part I am looking for. Hope it answers your question.
Regards,
Dan
Community SupportHi Dan_SP ,
Thank you for the accurate data and information you provided me in your reply. In this regard, I have done a test as a reference: first use append in Power Query to merge the two tables, and create two measures to find the duration and hours that are met under different conditions. Create a slicer measure to filter out different rows of hour, and get the following results:
Hour_Reuslt =
VAR cur_index =
MIN ( 'Table'[Index] )
VAR cur__S_hour =
SUM ( 'Table'[S_Hour] )
VAR cur_E_hour =
SUM ( 'Table'[E_Hour] )
VAR next_index =
CALCULATE (
SUM ( 'Table'[Index] ),
FILTER (
ALL ( 'Table' ),
'Table'[Key] = MAX ( 'Table'[Key] )
&& 'Table'[Index] > cur_index
)
)
RETURN
IF (
cur__S_hour == cur_E_hour,
SUM('Table'[S_Hour]),
IF (
SUM ( 'Table'[Index] ) < next_index,
cur__S_hour,
cur_E_hour)
)Min_Reuslt =
VAR cur_index =
MIN ( 'Table'[Index] )
VAR cur__S_hour =
SUM ( 'Table'[S_Hour] )
VAR cur_E_hour =
SUM ( 'Table'[E_Hour] )
VAR next_index =
CALCULATE (
SUM ( 'Table'[Index] ),
FILTER (
ALL ( 'Table' ),
'Table'[Key] = MAX ( 'Table'[Key] )
&& 'Table'[Index] > cur_index
)
)
RETURN
IF (
cur__S_hour == cur_E_hour,
SUM ( 'Table'[E_Min] ) - SUM ( 'Table'[S_Min] ),
IF (
SUM ( 'Table'[Index] ) < next_index,
60 - SUM ( 'Table'[S_Min] ),
SUM ( 'Table'[E_Min] )
)
)Slicer_ =
VAR cur_index =
MIN ( 'Table'[Index] )
VAR cur__S_hour =
SUM ( 'Table'[S_Hour] )
VAR cur_E_hour =
SUM ( 'Table'[E_Hour] )
VAR next_index =
CALCULATE (
SUM ( 'Table'[Index] ),
FILTER (
ALL ( 'Table' ),
'Table'[Key] = MAX ( 'Table'[Key] )
&& 'Table'[Index] > cur_index
)
)
RETURN
IF(cur__S_hour==cur_E_hour && cur_index <next_index,1,0)
If the problem is still not resolved, please provide detailed error information and let me know immediately, looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Sorry for the delayed response.
Thanks for your help. I think this should work, let me check and get back to you.
Regards,
Dan
Also v-henryk-mstf could you please attach that sample pbix file, as I am not able to access the onedrive link you shared.
Regards,
Danish
- v-henryk-mstf
Hi Dan_SP ,
Sorry it is my mistake, now this is the correct open link:
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.