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:
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 =]
