Split duration into hourly basis and also split dates when the process overflows the next day

[Dan_SP]

Hi All,

I have a problem where in need to split the start and end time in hourly basis and find the duration for which the machine was busy, below is the sample data which i have. Preferably need this is in power query

Key	Start Time	End Time	Machine Name
x1	2020-01-16T19:30:00.00Z	2020-01-16T19:33:00.000Z	M1
x2	2020-01-16T19:45:00.00Z	2020-01-16T19:50:00.000Z	M1
x3	2020-01-17T22:45:00.00Z	2020-01-17T23:05:00.000Z	M2
x4	2020-01-17T23:55:00.00Z	2020-01-18T00:15:00.000Z	M2
x5	2020-01-16T12:45:00.00Z	2020-01-16T12:55:00.000Z	M3

I want to be able to split the data per hour of the day to find the duration within that hour the machine is busy, below is the sample output i am expecting from which i could calculate the sum/avg duration for which a machine was busy during the hour of the day, also maybe particular day of week. I tried this link but was not able to get the split in duration. 

Key	Start Time	End Time	Machine Name	Date	Hour	Duration Busy (mins)
x1	2020-01-16T19:30:00.00Z	2020-01-16T19:33:00.000Z	M1	2020-01-16	19	3
x2	2020-01-16T19:45:00.00Z	2020-01-16T19:50:00.000Z	M1	2020-01-16	19	5
x3	2020-01-17T22:45:00.00Z	2020-01-17T23:05:00.000Z	M2	2020-01-17	22	15
x3	2020-01-17T22:45:00.00Z	2020-01-17T23:05:00.000Z	M2	2020-01-17	23	5
x4	2020-01-17T23:55:00.00Z	2020-01-18T00:15:00.000Z	M2	2020-01-17	23	5
x4	2020-01-17T23:55:00.00Z	2020-01-18T00:15:00.000Z	M2	2020-01-18	0	15
x5	2020-01-16T12:45:00.00Z	2020-01-16T12:55:00.000Z	M3	2020-01-17	12	10

Any help it terms of data manipulation and visualization (summarized hourly view/ week of the day view) to get the desired result of showing the period during which each  machine is busy and available would be greatly appreciated.

For now I have visualized the data as attached but I need a summarized view and don't think this is the best way to visualize it.

Thanks in advance

Dan

[v-henryk-mstf]

Hi @Dan_SP ,

After reading your question, I am a little confused. I want to know what the calculation logic is in the screenshot below. The same result cannot be obtained with End-Start in the marked lines. I hope you can provide detailed data information and expected results to facilitate my further testing.

Best Regards,
Henry

[Dan_SP]

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

[v-henryk-mstf]

Hi @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)

Here is the sample pbix file.

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.

[Dan_SP]

Hi @v-henryk-mstf 

Sorry for the delayed response.

Thanks for your help. I think this should work, let me check and get back to you.

Regards,

Dan

[Dan_SP]

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.

[Dan_SP]

Hi @v-henryk-mstf ,

Your solution works when we view it as a table, but I want to be able to visualize it for eg. I would want the x-axis to be the Hours (0-23) and the y-axis the avg duration in mins for that hour. With the bot name as a filter so i can filter on the bot name and see what time usually in the day the bot is free so that i can add a new process to it.

Something like the image i have attached. Hope you got the point. 

Regards,

Dan

[amitchandak]

@Dan_SP , This how I have done in past. Might need to try if this approach did not work.

I created an hour table and and then create a new table with help from that

Generateseries(1:24)

add column

hour = time([Value],0,0)

Then follow the same approach in the file(attached after signature) that I have for date. You need to get the hour from this new table to get diff

[Dan_SP]

Hi @amitchandak , I tried your suggestions but was not able to get it work, not sure if I did it correctly as your thing is for calculating the day counts. I need to explore still the dax queries.