cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Resolver I

## Workschedule starttime sum hours per employee/day/type

Hi all,

I have a large workschedule table (see example blue table) for employees, only with start datetime (so no end datetime)

Now I want to know the scheduled hours per employee/day/type. See the white answer column for what I need.

Note that the first column dayscheduleid is a unique number column, ordered from oldest number to number.

The solution may be created as a calculated column in M (preferred). A DAX calculated column is also OK. A measure is not preferred.

see here the link to the blue table that you can use in PowerBI desktop: https://www.dropbox.com/scl/fi/c6kbneps1701l9u8505iz/Workschedule.xlsx?dl=0&rlkey=9kmaqjcf3tk82o9ezv...

Thanks!

1 ACCEPTED SOLUTION
Resolver I

I found a solution myself using
1) create a new Index column

2) add calculated column next row = LOOKUPVALUE(Workschedule[startdatetime],Workschedule[Index],Workschedule[Index] + 1)

please note thet I need some extra logic for when the table goes from employeeID 2100 to the next ID 3500

3 REPLIES 3
Resolver I

I found a solution myself using
1) create a new Index column

2) add calculated column next row = LOOKUPVALUE(Workschedule[startdatetime],Workschedule[Index],Workschedule[Index] + 1)

please note thet I need some extra logic for when the table goes from employeeID 2100 to the next ID 3500

Super User

Hello @FilipFireFlyBI ,

check if this solution would work for your case
https://youtu.be/6JA6uy3ZRBA

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

 Did I answer your question? Mark my post as a solution! Appreciate your Kudos
 Vote for my Community Mobile App Idea

Proud to be a Super User!

Resolver I

thanks for fast reply but that was not the solution.
reason is that in my problem I need the next row.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors