cancel
Showing results 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.

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
MVP

Hi,

maybe this Power Query calculation helps.

Please mark my post as solution, this will also help others.

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."

Anonymous
Not applicable

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?

MVP

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

Please mark my post as solution, this will also help others.

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."

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!

Announcements

#### 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

#### Fabric Community Update - April 2024

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

#### Power BI Monthly Update - March 2024

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

Top Solution Authors
Top Kudoed Authors