Forum Discussion
Helper IILine chart counting duration based on a start/End time
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!
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.
5 Replies
- amitchandak
Super User
DreDre , very similar to this blog. refer if this can help.
An hourly bucket Table will act as a date table
DreDreThis is not quite what I was trying to accomplish as I am trying to do time throughout a day vs dates.
- DataZoe
Microsoft Employee
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.
- 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.
