Hello, I need help to be able to classify the hours worked by the employees of a company into a time range (from 0 to 6, from 6 to 12, from 12 to 18 and from 18 to 24) ( You have an employee table with the hours worked by date). I developed the example in excel, but I need to do the same in dax, powe pivot or power bi.
For example, if an employee worked :
attached the file in excel
hours worked.xlsb (11.5 KB)
This can be accomplished through DAX or M, I chose to go with M and add the hours worked in the transform steps.
let
Source =
#“Changed Type” = Table.TransformColumnTypes(Source,{{“Emp ID”, Int64.Type}, {“Date”, type date}, {“From”, type time}, {“To”, type time}}),
#“Added Custom” = Table.AddColumn(#“Changed Type”, “To Date”, each if [To] < [From] then Date.AddDays( [Date], 1 ) else [Date]),
#“Changed Type1” = Table.TransformColumnTypes(#“Added Custom”,{{“To Date”, type date}}),
#“Inserted Merged Date and Time” = Table.AddColumn(#“Changed Type1”, “From DateTime”, each [Date] & [From], type datetime),
#“Inserted Merged Date and Time1” = Table.AddColumn(#“Inserted Merged Date and Time”, “To DateTime”, each [To] & [To Date], type datetime),
#“Inserted Time Subtraction” = Table.AddColumn(#“Inserted Merged Date and Time1”, “Duration”, each [To DateTime] - [From DateTime], type duration),
#“Removed Columns” = Table.RemoveColumns(#“Inserted Time Subtraction”,{“To Date”, “From DateTime”, “To DateTime”}),
#“Inserted Hours” = Table.AddColumn(#“Removed Columns”, “Hours Worked”, each Duration.Hours([Duration]) + (Duration.Minutes([Duration])/60)),
#“Changed Type2” = Table.TransformColumnTypes(#“Inserted Hours”,{{“Hours Worked”, type number}})
in
#“Changed Type2”
NOTE: I removed the source step in the quote above, because that was me typing in the source table into PowerBi, no need to see that here 
From there, the DAX is simple, add up the hours worked.
Total Hours Worked = SUM( Schedule[Hours Worked] )
Full Solution is attached. I did add an additional employee (Jane) to demonstrate how this solution can include partial hours.
Thank you very much Heather! but I think there’s one more step to go; I would like to know, how the hours have been distributed in the indicated ranges:
For example, 5 hours have been worked here, but 1 has been worked in the range of 0:00 am to 6:00 am and 2 have been worked in the range of 6:00 am to 12:00
the final idea is to be able to display a time distribution chart in different time ranges:
I appreciate the effort you’ve made to help me.
Hi @okat, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.
I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.
Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.
So sorry to be late getting back to you - we had a rather major blowup at work and I just haven’t had much extra time lately. And many thanks to @Rajesh for the DAX solution which is probably more efficient.
Before coming back to the forum this morning, I did finish the solution I had started for you, so to not waste that effort (and demonstrate that there is an M solution for this as well), here is my solution.
Hours Worked - Power Query solution 2.pbix (81.2 KB)
I created four Parameters, labeled “R1” to “R4”, with the start time for each of your periods.
This will allow you to control the time ranges from one location, and it will adjust the entire report based on your changes. (NOTE, if you decide to ADD a range, a change will need to be made in two additional locations)
Next, I added an index to what I am now calling the “Schedule Master”, and I added a date for the end period (To Date).
That table was referenced for a new table “Schedule Periods”
In the new “Schedule Periods” table, I created a list of of the time range (in quarter hours), and assigned the time Ranges based on a simple nested IF statement (this is one of the spots you will need to add logic if you add additional ranges)
Next, I created a “Time Periods” table from the parameters created in the first step (this is another spot you will need to make a small change to if you add additional ranges)
Finally, I loaded this to the report, hid several columns, and added a simple measure to count the rows of the Schedule Periods.
Excellent Heather! This is definitely what I expected, thank you for your time and delivery. Thanks a lot!
Thank you very much Rajesh! , you’ve given me a great vision to solve this!
happy to help 
