SUM of hours within a shift of employee

[Anonymous]

Hey guys,

Im wondering if theres a possibility to calculate the written hours of our employees within their shifts

I have created this in SQL form, but its an very intensive SQL to run

In Table 1 the shifts are beeing saved, every employee has an own entry for each day he or she works,

In Table 2 the time captured is saved, so every employee writes their hours during the timeframe of his shift.

Theres a catch though; the shifts are saved in local time but the time captured is saved in UTC. Also since some shifts are not within one day, like a nightshift: 20:00-6:30.

TABLE 1 (Shift information)

END      |  START   | EMPLOYEE
datetime | datetime | varchar

TABLE 2 (Written time)

END      |  START   | DURATION | EMPLOYEE
datetime | datetime | duration | varchar

 Is there a dax formule for this calculation?

Any help would be highly appreciated!!

Regards,

Wessel

[mwegener]

Hi,

maybe this Power Query calculation helps.

If I answered your question, please mark my post as solution, this will also help others.

[Anonymous]

Thanks for your reply, unfortunately that does not answer the question.

I am able to calculate the lenght of the shifts. However i want to know how much time has been written in between the start and the end time of the shift.

What would be a good formula for this calculation?

[mwegener]

Hi @Anonymous

you could make a relative join between written and shift table and calculate the gap between written and shift time.

    #"Relative Merge" = Table.AddColumn(#"Changed Type", "RelativeJoin",
        (Written) => Table.SelectRows(Table1,
            each Written[EMPLOYEE]=[EMPLOYEE] 
			 and (  (Written[START]<=[START] and Written[END]>=[START])
			     or (Written[START]<=[END] and Written[END]>=[END])
				 or (Written[START]>=[START] and Written[END]<=[END])
				 ) 
			)
        )

[Anonymous]

Im sorry, Im not so familiar with the Power Query and powerbi in general.

I pasted the info in my table  like this:

#"Relative Merge" = Table.AddColumn(#"Changed Type", "RelativeJoin",
        ("Total WO and ARID") => Table.SelectRows("Shift lenght",
            each "Total WO and ARID"[user_sign]=[user_sign] 
			 and (  ("Total WO and ARID"[start]<=[start] and "Total WO and ARID"[end]>=[start])
			     or ("Total WO and ARID"[start]<=[end] and "Total WO and ARID"[end]>=[end])
				 or ("Total WO and ARID"[start]>=[start] and "Total WO and ARID"[end]<=[end])
				 ) 
			)
        )

it is saying that in this row: ("Total WO and ARID") => Table.SelectRows("Shift lenght", A token identifier is expected.

What does this mean? What can i do?

Thanks!