Forum Discussion
Line chart counting duration based on a start/End time
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.
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.
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.