How to plot a line chart related to the count of an entity during a period of a time

[johnyip]

Hi all, 

I have the below mock data.

ID	Shift Start Time	Shift End Time
1	18/09/2023 22:00	19/09/2023 07:30
2	23/09/2023 06:30	23/09/2023 16:00
3	18/09/2023 22:00	19/09/2023 04:00

The data is recording the starting and ending shift time of different staff. The table is simple and explains itself and I don't think I have to explain it further.

My issue is to plot a line chart as per the above data (x-axis: time, y-axis: count of staff), in order to show the number of staff available at each exact time frame.

For example, during 18/09/2023 22:00 and 19/09/2023 04:00, two staffs (ID=1,3) are available, so in the line chart, it will show 2. For the remaining relevant time, it will show 1.

This is basically a line chart showing the distribution of staff in a shift roster.

One additional requirement is in the x-axis, I need each tick of the time to be 5 minutes (which I think I can achieve this, but I will be very thankful if anyone can also do that in their solution)

Can anyone show me how to achieve this? Thanks.

[johnyip]

Using Excel, I prepared the data like the following:

ID / Time	18/09/2023 00:00	18/09/2023 00:05	18/09/2023 00:10	18/09/2023 00:15	...
1	0	0	0	1	...
2	1	1	1	0	...
3	1	1	0	0	...
...	0	1	1	0	...

And then when I try to import it into PowerBI and then unpivot it in PowerQuery, memory error takes place.

Since there seems no one having ideas on how to plot that line chart and my attempts to load the data into PowerBI is not successful, I will regard this is not possible in PowerBI.

[johnyip]

Anyone know the tricks? Or that is not possible at all in the PowerBI level?