Calculate with a start and finish time, how many employees are working at a certain time.

[brief001]

I want to be able to measure how many FTEs (employees) are working at the times from the table "Table_Time_Interval".

The FTEs (employees) who are working are recorded in the table "Table_Roster". Where column "Time Type" must have the value "worktime".
And with the columns Start and Finish you should be able to calculate whether they are active at the times from the table "Table_Time_Interval".

The following must be taken into account: In the example you can see that James has a "Time Type" with the value "non availability" from 8.00 to 9.00.
As a result, the time hour 8.00 should not be returned, even though it has a "Time Type" with the value "worktime" from 8.00 to 17.00

The code of the two fact tables:

Table_Roster

let
Bron = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMTS1W0lEqzy/KLsnMTQUyQ1ITcxWCUlOATCNDXUMDXSMjBNPAyEjBwMLKwABVyNAcJBSrgzAwNzW1JDMvnUzzDCzh5uVn5FHVfRDzSHCeoQGmcYYw44Izc/NJdZ+hEYaBRhgG5pTmJWckFaUmZhNnpimmI81gZroVgXWiu9EppzSVvECEGogWiKSaB43kWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Time Type" = _t, #"Team Name" = _t, Date = _t, Start = _t, Finish = _t]),
#"Type gewijzigd" = Table.TransformColumnTypes(Bron,{{"Name", type text}, {"Time Type", type text}, {"Team Name", type text}, {"Date", type date}, {"Start", type datetime}, {"Finish", type datetime}})
in
#"Type gewijzigd"



Table_Time_Interval

let
Bron = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdE7CoAwFETRrYTUCeSNf5chdpJC0NL9t4ppZMBMlzzmVHfb/Hrul1vOwwcPi5Yi8DzHOaXPxb3/HP7mE82n+twS7cuhAoyBCQAGEKBh0AjQMmgF6Bh0AvQMegEGBoMAnNlEZ+PQJkqDS0OUBpeGKA0uXQ453w==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Team Name" = _t, Date = _t, Time = _t, Interval = _t]),
#"Type gewijzigd" = Table.TransformColumnTypes(Bron,{{"Team Name", type text}, {"Date", type date}, {"Time", type time}, {"Interval", type datetime}})
in
#"Type gewijzigd"

[brief001]

I thought I could count the numbers of "worktime" myself with the formula below, but I'm stuck on the value of date-time. Does anyone have a suggestion how I can still filter on the start date-time and finish date-time?

If I can create a counter on "worktime", then I can copy it for "non availability". And by subtracting these from each other I get the correct result.

[eliasayy]

hello its tricky for me but i can share what i got till now:
first go to tranform data and then to your roster table
add the following :

Start Hour = Time.Hour([Start])

End Hour = Time.Hour([Finish])

  

then add list of those hours:

List = {Number.From([Start Hour])..Number.From([End Hour])}

then expand to new rows

you will get a list of their hours 
now go to interval and add the hours column same as above

Hours = Time.Hour([Interval])

after that make sure both are whole number type

then create an inactive relationship between both added column hours and make this measure:

availibilty = CALCULATE(COUNT(Roster[Team Name]),FILTER(Roster,Roster[Time Type] = "worktime"),USERELATIONSHIP(Time_Interval[Custom],Roster[Hours]))

as you see it counts perfectly 

but you have work time overlaping with meetings or breaks so add new measure :

not availibilty = CALCULATE(COUNT(Roster[Team Name]),FILTER(Roster,Roster[Time Type] <> "worktime"),USERELATIONSHIP(Time_Interval[Custom],Roster[Hours]))

and now just create final measure :

Total = [Available]-[Not Available]

 

my only problem is that it is taking for example break from 8 to 9 as 8 and 9 not available instead of only at 8 not available
i hope i couldve helped you reach one step closer to your goal

[brief001]

Hi eliasayy, thanks so far.

I'm stuck on your action to 'add list of those hours'. Otherwise can you give me the code of the advanced editor in Power Query Editor?

[eliasayy]

Hello im sorey i dont have my computer on me, but i can help you through it. In the table roster, write the codes i gave to you to give the hour of start and finish dates.afyer that, Make sure the type is whole number. Then add another column and write the code i gave you to make a list. The lost should be between the columns i instructed you to add then expand to new rows

[brief001]

I have now succeeded with your additional supplement. And now I see that it's just not exactly what I'm looking for. The problem is in the end time, he also counts this. Perhaps I can solve it by counting down one minute at the end time.

[eliasayy · Accepted solution]

@brief001 i finally found the solution

i will share with you the powerbi i used inorder for you to see the steps in details.

but i will still share how i did it,
after created the extra columns from power query and expnding the list of hours,
go back to dax,
on the roster table,
add a new calculated column and filter to only show the max value of non worktime:

Maxnonworking = CALCULATE(MAX(Roster[Hours]),FILTER(ALLEXCEPT(Roster,Roster[Name]),Roster[Time Type]<>"worktime"))

 

after you created the calculated column 
create a new measure for the not available people :

not availibilty = CALCULATE(COUNT(Roster[Team Name]),FILTER(Roster,Roster[Time Type] <> "worktime"),FILTER(Roster,Roster[Hours]<>Roster[Maxnonworking]),USERELATIONSHIP(Time_Interval[Custom],Roster[Hours]))

after that you got your total of available - non available people and you get your result
never the less here are the links to the powerbi and excel file:
pbix : https://1drv.ms/u/s!Ag9tIyk2ofNRjlYLQU7dP7ifIDv7?e=Fa0iar

excel : https://1drv.ms/x/s!Ag9tIyk2ofNRjleKjjoq0mcHbvM8?e=JcDgoE

[brief001]

Hi @eliasayy ,

What an effort you put in to answer my question. I think this is really very nice of you. And so nice that you also explain in great detail how you came to the solution. A thousand thanks for this!

Besides the fact that your solution works great, I find it annoying that my table Roster is getting very large in terms of records.

-- --

I came up with the DAX formula below myself. However, this goes wrong as soon as I get data from, for example, October 22 in both fact tables. Then my DAX formula only works if I add filter visualization in my dashboard where I always have to select a day. Selecting this is not what I want. Because my goal is that he would then return a summation of the dates.

Count Worktime =

VAR Interval = SELECTEDVALUE(Table_Time_Interval[Interval])

VAR NewTable =

CALCULATETABLE(

    GROUPBY(Table_Roster,

        Table_Roster[Team Name],Table_Roster[Name],Table_Roster[Time Type]),

        FILTER(Table_Roster, Table_Roster[Time Type] = "worktime"         ),

        FILTER(Table_Roster, Table_Roster[Start]   <= Interval              ),

        FILTER(Table_Roster, Table_Roster[Finish]   > Interval              ))

VAR Result =

COUNTROWS ( NewTable  )

RETURN

Result

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

Count Non Worktime =

VAR Interval = SELECTEDVALUE(Table_Time_Interval[Interval])

VAR NewTable =

CALCULATETABLE(

    GROUPBY(Table_Roster,

        Table_Roster[Team Name],Table_Roster[Name],Table_Roster[Time Type]),

        FILTER(Table_Roster, Table_Roster[Time Type] <> "worktime"         ),

        FILTER(Table_Roster, Table_Roster[Start]     <= Interval           ),

        FILTER(Table_Roster, Table_Roster[Finish]     > Interval           ))

VAR Result =

COUNTROWS ( NewTable  )

RETURN

Result


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

Total = [Count Worktime] - [Count Non Worktime]

[brief001]

Hi @eliasayy ,

Can you perhaps think of a solution how my DAX formula could work, without having to select or filter a specific day in my Dashboard?

https://we.tl/t-C4obpZpxSf

Greetings, Bas.

[brief001]