Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
Anonymous
Not applicable

SUM of hours within a shift of employee

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

4 REPLIES 4

Hi,

maybe this Power Query calculation helps.

2018-11-29 07_36_45-Unbenannt - Power Query-Editor.png

 

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

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Anonymous
Not applicable

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?

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])
				 ) 
			)
        )

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Anonymous
Not applicable

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!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.