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

     

     

     

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

     

     

     

    • EnochS's avatar
      EnochS
      Advocate II
      8 years ago

      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!