Workschedule starttime sum hours per employee/day/type

[FilipFireFlyBI]

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!

[FilipFireFlyBI · Accepted solution]

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

[Idrissshatila]

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 👍

Follow me on Linkedin

[FilipFireFlyBI]

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