Calculate Total Bonus Time

Hi guys, I need some help on a hopefully easy piece of code. But it has a longer explanation that I would like... sorry for the wall - I tried to cut it down to be as short as possible.

I am trying to calculate total bonus time for a team, using the following tables:

• Table 1:
  • Name: Adam, Bob, Carl
  • Start Date: Thursday 1 June 2017, Thursday 1 June 2017, Monday 1 October 2018
  • Agent.Type: Special, Regular, Special
• Table 2:
  • Date: Monday 26 November 2018, Monday 26 November 2018, Tuesday 17 November 2018
  • Name: Adam, Bob, Bob
  • Time.Type: Bonus, Overtime, Overtime
  • Hours: 8, 2, 2
    • Aka: Adam used 8 hours of his bonus (1 working day) , Bob did 4 hours of overtime & Carl hasnt done anything noteworthy yet.
• Date Table:
  • All the usual stuff

This is the situation:

• Special people get 1.5 bonus days per month and regular people get 2 days in total per year.
• Bonus time does not carry over to the next year.
• So from the above table
  • Adam would have 1.5*12 bonus days for this year (2018) - from the begining of the year, to the end.
  • Bob would have a flat output of 2 bonus days
  • and Carl would have 1.5*3 bonus days for 2018 - from Oct to the end of Dec.

This is what I am trying to calculate:

• Total Bonus Days: filterable by a slicer, such as - bonus days gained on month, by person (this one isnt thaaat important).
• Total Bonus Days Remaining: filterable by a slicer, such as - who has X bonus days remaining (important).
• Total Bonus Days Used: filterable by slicer, such as - used in what month, by which person (important).

Now, I have been able to calculate most of this myself, but my sloppy workaround uses loads of measures which dont really work with a slicer.

This is how i did it:

Measure 0:

• Days Sum = SUM('Table 2'[Hours])/8

Measure 1:

• Total Bonus Taken = CALCULATE ( [Days Sum] , 'Table 2'[Time.Type] = " Bonus " )

Measure 2:

• Total Special Bonus = CALCULATE ( [Total Months] , 'Table 1'[Agent.Type] = " Special " ) *2

Measure 3:

• Total Regular Bonus = CALCULATE ( COUNTROWS ('Table 1'), 'Table 1'[Agent.Type] = " Regular " ) *1.5

Measure 4:

• Total Bonus Available = ( [Total Special Bonus] + [Total Regular Bonus] ) - [Total Bonus Taken]

From these, I can see (and slice) Total Bonus Days Taken. I can also see Total Bonus Available, but I cannot slice it by name (so idk how many bonus days Carl has left, for example)

! OK ! Thats my long storybook of a question!

I have re-read this a bunch of times now, and I hope it all makes sense. But please do not hesitate to ask questions and I can help out as much as possible.

I would be greatly appreciative of anyone that could help out. This is all super complex for me, at least at the skill level I have atm, but hopefully this is a pretty easy fix for some of the datanaughts out there.

Kindest regards,

Stef =]