Line chart counting duration based on a start/End time

[DreDre]

Long story short, I have start/end times in different rows and I am trying to create a line chart that gives a total number of people that fall between these two points so that I know how many people are working.

For example:

Person	Start	End
1	08:00	16:30
2	08:05	16:35
3	12:00	20:30
4	14:30	22:00
5	15:00	23:30
6	16:00	00:30

If I were to look at 12:00, I should have 3 people working, then at 15:00 I would have 5 and finally at 18:00 I should have 4. My goal is to be able to look at every minute of the day and see how many people are working based on the Start/End columns in a table. Any help or guidance would be very helpful!

[DataZoe · Accepted solution]

@DreDre 

Is this what you are going for:

For this I would create a calculated table giving me each minute of the day:

TimeTable =
VAR HourTable =
    SELECTCOLUMNS ( GENERATESERIES ( ( 0 ), ( 23 ) ), "Hour", [Value] )
VAR MinuteTable =
    SELECTCOLUMNS ( GENERATESERIES ( ( 0 ), ( 59 ) ), "Minute", [Value] )
RETURN
    ADDCOLUMNS (
        CROSSJOIN ( HourTable, MinuteTable ),
        "Time", TIME ( [Hour], [Minute], 0 )
    )

(modified from here: https://kohera.be/blog/power-bi/how-to-create-a-time-table-in-power-bi-in-a-few-simple-steps/ )

That would be the x-axis and I wouldn't create a relationship to this table!

I would then create a measure to count how many people are working at each time, like this:

Active Workers =
CALCULATE (
    DISTINCTCOUNT ( Hours[Person] ),
    FILTER (
        Hours,
        Hours[Start] <= MIN ( TimeTable[Time] )
            && Hours[End] > MAX ( TimeTable[Time] )
    )
)

and then put it on the line chart.

Edit: Also make sure all the time fields are in the "Time" datetype.

[DreDre]

I am working on trying to adapt this for my dataset, but yes, your image is exactly what I am trying to accomplish! Thank-you so much @DataZoe for the help! This one has been bothering me.

[DataZoe]

@DreDre here is the pbix too: https://github.com/DataZoe/PBIX/blob/master/Active%20Hourly%20Workers.pbix The only data structure change I could see was the worker who went to 12:30AM, may need to have 2 entries, one until 11:50pm then a second one from 12:00am to 12:30am, or something like that.

[amitchandak]

@DreDre , very similar to this blog. refer if this can help.

An hourly bucket Table will act as a date table

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[DreDre]

This is not quite what I was trying to accomplish as I am trying to do time throughout a day vs dates.