cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

Calculated Column - Overtime Hours Weekly & Daily

Hello,

 

I was trying to find similar posts but non of them seamed to resolve my particular scenario.

I need to create a calculated column (or any alternative in Dax or Query editor) to be able to know how many hours were overtime and for those hours to be on the same row as the job #/"station". The reason for this is because this is for a construction company and we need to be able to know what the actual cost of the each job is, to include overtime, since overtime is a normal occurance. I need to be able to create a column with accumulated hours so that I would be able to create a second column to determine anything over 40 hours and then subtract from 40 to be able to get the OT hours and Regular hours etc.. If there is a better way to do this, please advise.

 

Here is the example that I created in excel with formulas:

Example.PNG

 

Here is the file with the sample data:

Excel Sample.xlsx

2 ACCEPTED SOLUTIONS
Microsoft
Microsoft

HI @EnochS

 

The following calculated column gets close to your first requirement.  The problem is, we need another column to split the shigts  where the user has two entries on the same day.  This is to allow you to identify the shift where you step into overtime.

 

If you don't have another ID column, perhaps add an index column in the query editor.  Let me know if you need help with this

 

Cumulative Hours for week = 
    SUMX(
        FILTER(
            'Table3',
            'Table3'[User ID] = EARLIER('Table3'[User ID]) &&
            Table3[Week #] = EARLIER('Table3'[Week #]) &&
            'Table3'[Date] <= EARLIER('Table3'[Date])
            ),
            'Table3'[Hours])

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Hi @EnochS

 

If you use the "Add an index" feature in the Query Editory (becareful to sort your data first!)

 

Then you can add the column to the calculation.  I have shown it here in bold

 

Cumulative Hours for week = 
    SUMX(
        FILTER(
            'Table3',
            'Table3'[User ID] = EARLIER('Table3'[User ID]) &&
            Table3[Week #] = EARLIER('Table3'[Week #]) &&
            'Table3'[Date] <= EARLIER('Table3'[Date]) &&
            'Table3'[Index] <= EARLIER('Table3'[Index])
            ),
            'Table3'[Hours])

The cumulative hours column can now be split, with the last one used to drive your TRUE column and see how much overtime etc

 

image.png

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

10 REPLIES 10
Resolver IV
Resolver IV

Hi @EnochS,

I have added an index column to your data as suggested by @Phil_Seamark and used a slightly different method for the calculated column Cumulative Weekly hours. 

Cumulative Weekly Hours = CALCULATE(
                                              SUM(Sheet1[Hours]),
                                               ALLEXCEPT(
                                              Sheet1,Sheet1[User ID],Sheet1  [Week #]),
                                              Sheet1[Index]<=EARLIER(Sheet1[Index]))

and then for the calculated column for overtime hours

OT Hours = ROUND(MIN([Hours], MAX([Cumulative Weekly Hours]-40,0)),2)

and the calculated column for regular hours 

Regular Hours = [Hours]-[OT Hours]

Sample 02-21-2018.PNG

Thank you @MarkS. I studied your use of ALLEXCEPT and it is helpful to know how to write these DAX formulas for different cases.

Microsoft
Microsoft

HI @EnochS

 

The following calculated column gets close to your first requirement.  The problem is, we need another column to split the shigts  where the user has two entries on the same day.  This is to allow you to identify the shift where you step into overtime.

 

If you don't have another ID column, perhaps add an index column in the query editor.  Let me know if you need help with this

 

Cumulative Hours for week = 
    SUMX(
        FILTER(
            'Table3',
            'Table3'[User ID] = EARLIER('Table3'[User ID]) &&
            Table3[Week #] = EARLIER('Table3'[Week #]) &&
            'Table3'[Date] <= EARLIER('Table3'[Date])
            ),
            'Table3'[Hours])

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

@Phil_Seamark, thank you! it is working as expected. I can see what you mean about the shifts. What would your recommendation be regarding for which type of columns to use for a unique index? What would the calculation look like AFTER I created the index? 

Hi @EnochS

 

If you use the "Add an index" feature in the Query Editory (becareful to sort your data first!)

 

Then you can add the column to the calculation.  I have shown it here in bold

 

Cumulative Hours for week = 
    SUMX(
        FILTER(
            'Table3',
            'Table3'[User ID] = EARLIER('Table3'[User ID]) &&
            Table3[Week #] = EARLIER('Table3'[Week #]) &&
            'Table3'[Date] <= EARLIER('Table3'[Date]) &&
            'Table3'[Index] <= EARLIER('Table3'[Index])
            ),
            'Table3'[Hours])

The cumulative hours column can now be split, with the last one used to drive your TRUE column and see how much overtime etc

 

image.png

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Thank you, @Phil_Seamark!

 

Do you have any explanation or documentation on how you used these functions work? I see you used Filter & Earlier with &&. I started taking the SQLBI classes online and am currently learning about Filter context, but I still feel its a little over my head, but I'm slowly starting to grasp it. Any information would be helpful, but thank you for your time already!

I have a book coming out shortly that dives down into these functions and explains how they work in more detail

 

https://www.amazon.com/Beginning-DAX-Power-BI-Intelligence/dp/1484234766/

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

@Phil_Seamark, will you by chance be offering an E-Book version?? If so, I will definitely be interested in a copy because then I can read it on the go and be able to search for any term. If not, I'm still interested in this resouce. Thanks again for the help!

There is an E-book version coming out later on.  I can probably flick you the relevant chapter if you PM me your email (for free 🙂 )

 

https://www.apress.com/gp/book/9781484234761

 

 

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Bascially we need to split the columns highlighted

 

image.png

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors