Calculated Column - Overtime Hours Weekly & Daily

[EnochS]

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:

Here is the file with the sample data:

Excel Sample.xlsx

[Phil_Seamark · Accepted solution]

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])

[MarkS]

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]

[Anonymous]

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

[EnochS]

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 · Accepted solution]

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])

[EnochS]

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

[Phil_Seamark · Accepted solution]

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

[EnochS]

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!

[Phil_Seamark]

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/

[EnochS]

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

[Phil_Seamark]

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

[Phil_Seamark]

Bascially we need to split the columns highlighted