Forum Discussion

AppleMan's avatar
AppleMan
Helper III
2 years ago
Solved

Overtime Report Help

Hi,   I need some help designing the best way to make an overtime report work.   Right now, I have a table that has every labor entry employees have made. The important columns are: Employee num...
Need Help
  • rsbin's avatar
    rsbin
    2 years ago

    AppleMan ,

    Various ways this can be done.  I have attached an updated pbix that shows one of these ways.

    Import your Holiday Table.  Connect it to your Date Table.  It should be 1-1 relationship.

    Then I created a Calculated Column in your Date Table using SWITCH and LOOKUPVALUE.

    I structured it to give you 0's and 1's, but you can replace these with other values if you so choose ("Holiday" or "Work Day").

    Let me know if you run into any issues....

    Regards and Good Luck

rsbin's avatar
rsbin
Community Champion
2 years ago

AppleMan ,

First thing I would do is add a Calendar or Date Table if you have not already done so.  This is a PBI best practice and numerous videos are available to be had.
Once you have a proper Date Table, you will then need to define when your PayWeek starts.  Do you use Sun to Sat, Mon-Sun, or some other combination.  Here is a small data sample.

 

Join this Date Table to your Fact Table (Labor entries) via the Date Column.  You will then be able to Sum your Hours column by your defined PayWeek. Then create a measure or calculated column to determine OT.

Overtime = SumofHours - 40
Hope this gets you started.  I will continue to try to assist if you hit any hurdles along the way.

Regards,