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

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

     

     

     

11 Replies

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

    • EnochS's avatar
      EnochS
      Advocate II
      8 years ago

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

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

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    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])
    • EnochS's avatar
      EnochS
      Advocate II
      8 years ago

      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's avatar
        Phil_Seamark
        Microsoft Employee
        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

         

         

         

    • Phil_Seamark's avatar
      Phil_Seamark
      Microsoft Employee
      8 years ago

      Bascially we need to split the columns highlighted