Overtime by multiple states suggestions

[Anonymous]

Hello,

I am in the development phase of putting together a report to calculate overtime for multiple states. I have a reference table with all the states overtime rules with daily ot, daily dt, weekly ot, and 7th day ot along with each effective and end dates. I plan on merging that with the payroll data I have and multiply by its respective OT type. 

Has anyone performed a similar task, or have any suggestions on this process?

(Edit)

I would like to do most if not all of the overtime calculation within the query editor.

[v-lionel-msft]

Hi @Anonymous ,

"I plan on merging that with the payroll data I have and multiply by its respective OT type."

What are the problems you met during the development of the report? Merging data or creating DAX measures?

Please provide the sample data and expected output.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I do not have any problems with formulas at the moment, if I do I will post another question. However, I would like to hear how others have approached this process. This is something out of the norm for me and I am using a process I am familiar with. If anyone has another approach that would be much more suitable, I would be more than happy to adopt that approach.

As for my data set I have 2 tables, an Overtime rules table and the hours worked for each employee. I have a join on State that brings in the number of hours that qualify for Daily OT (1.5), Daily OT (2x), Weekly OT, then a few conditional subtraction formulas. I have a duplicate connection to the employee hours table where I group by Week of and sum total hours. I then subtract total week hours with the Weekly OT rule and multiply that by the rate at 1.5.

Conditional Subtraction formulas

1. OT2 Subtraction = min(0, Total Hours - Day OT (2x))
2. OT1 Subtraction = min(0, Total Hours - OT2 - Day OT (1.5)
3. OT2 Hours = if Day OT (2x) is null then 0 else OT2 Subtraction
4. OT1 Hours = if Day OT (1.5) is null then 0 else OT1 Subtraction
5. Reg Hours = Total Hours - OT1 - OT2
6. OT2 Amount = Rate * 2 * OT2 Hours
7. OT1 Amount = Rate * 1.5 * OT1 Hours
8. Reg Amount = Rate * Reg Hours

Overtime Rules table:

Employee Hours Worked Table:

[parry2k]

@Anonymous Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490