Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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.
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
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.
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
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.
@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.
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
@DreDre , very similar to this blog. refer if this can help.
An hourly bucket Table will act as a date table
This is not quite what I was trying to accomplish as I am trying to do time throughout a day vs dates.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |