# Distribution of hours worked by employees en DAX

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.

Hours Worked - Power Query solution.pbix (188.5 KB)

3 Likes

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.

• Use the forum search to discover if your query has been asked before by another member.
• When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.
• Use the proper category that best describes your topic
• Provide as much context to a question as possible.
• Include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

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.

@okat

Is this what you are expecting

Distribution of hours.pbix (30.9 KB)

1 Like

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.

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

2 Likes

Excellent Heather! This is definitely what I expected, thank you for your time and delivery. Thanks a lot!

1 Like

Thank you very much Rajesh! , youâ€™ve given me a great vision to solve this!

happy to help