Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
EnochS
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
Phil_Seamark
Employee
Employee

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

11 REPLIES 11
MarkS
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

@MarkSThis is a great solution and works perfectly. Is there a way to do all of this in measures? My data set is HUGE, and the resolution time is too long via calculated columns. If yes, could you provide a solution?

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

Phil_Seamark
Employee
Employee

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!

@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!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.