Forum Discussion

EnochS's avatar
EnochS
Advocate II
8 years ago
Solved

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...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    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])
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    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

     

     

     

MarkS's avatar
MarkS
Resolver IV
8 years ago

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's avatar
Anonymous
Not applicable
3 years ago

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?