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 ,

Does this work for you?

Threshold = 40 - 
            ( 8 * LOOKUPVALUE( TestDate[HolidayWeek], TestDate[FullWeeknum], [FullWeeknum] ))

FullWeeknumHoursThreshold

317 41 32
319 5 24

My test data as follows:  Week 317 has 1 holiday, Week 319 has 2 holidays.

Regards,

AppleMan's avatar
AppleMan
Helper III
2 years ago

What is the final variable '[fullweeknum]' in reference to? 

I am not able to try this formula as its typed. I can enter everything as you show except that last variable to test it. 

  • rsbin's avatar
    rsbin
    Community Champion
    2 years ago

    AppleMan ,

    Column Name from my dummy table:  TestThreshold[FullWeekNum]

     

  • AppleMan's avatar
    AppleMan
    Helper III
    2 years ago

    I found my issue, I still had the whole function wrapped in calculate. I removed that and this seems to work how I want! Thank you!