accumulated, stacking

[kaio12foguim]

Hi guys, I have a problem and I can't solve it.

I need to make a line graph, with data stacked, accumulated, like a queuing theory.

--------------------------------------

this data refers to team shifts,

example: I have a monthly shift table

name

start shift

end shift

name	start shift	end shift
kaio	06:00	15:00
joao	06:00	16:00
marcos	07:00	18:00
felipe	07:00	19:00

I have the shift table, and I have a 1 in 1 hour table that I use as a filter.

example:

hour

00:00

...

06:00

...

15:00

...

00:00

in the data model, shift table shift start and time table hours fields are linked,
powerbi doesn't allow linking end of shift field in shift table to time field of timetable together

I'm trying to create a graph that shows the number of teams that there were at each hour, including those that entered and those that left.

I already managed to make the data stacked, but it stacks until the last hour, and the big problem is that I can't count the shifts that are ending

the code -

measure entry = COUNT('shift'[hour start])

measure exit = COUNT('shift'[hour exit]) -- here in I already have a problem, I put it to count by the end of shift field, but it returns the same start values, because in model the linked fields are the start of shift of the shift table in the hours field of the time table

measure accumulated =

var accumulateentry = CALCULATE(COUNT(shift[start shift]), FILTER(ALL(hour), hour[hour]<=MAX(hour[hour])))

return acucumulateentry 

the big problem I see is that I can't count the finished shifts, because in the model tab the fields that are being used to link a table at the time is the start of shift field, I've already tested inverting and putting the end of shift field , then I get the end of shift times, but I can't get count the start times anymore, it has to be one or the other

 I already tried to use the measure code CALCULATE(SELECTEDVALUE('shift'[hour end]), CROSSFILTER('shift'[hour end],'hours'[hour],Both)), but it doesn't work

[amitchandak · Accepted solution]

@kaio12foguim , You need the same approach with hour table one that has been done with a date here in this blog , refer current employee

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