Calculating work days with different RDOs

[eburke]

Hi all,

Very new to Power BI here so please bare with me.  I have a calendar which has work days/ non work days set up as 1 and 0 based on public holidays and weekends.  However I can't apply this to all employees as some have Rostered Days Off and these are different for different employees.  I think I can use some of the measures I've seen to calculate number of working days between to dates for those employees with no RDOs, but how do I get the calculation to look up a different column in the calendar data that has the 1 and 0 set up to show the different RDOs based on the name of the employee I want to calculate the dates for? Below is an example of the calendar I has set up.  Work Days Norm is for employees with no RDOs.  MondaysOff is the calendar for employees who have every Monday as an RDO.  I'm not even sure if this is the best way to approach this.  Any suggestions would be appreciated.  Thanks, Emma

Date	Day Name	Weekday	Working Days Norm	MondaysOff
1/01/2017	Sun	1	0	0
2/01/2017	Mon	2	0	0
3/01/2017	Tue	3	1	1
4/01/2017	Wed	4	1	1
5/01/2017	Thu	5	1	1
6/01/2017	Fri	6	1	1
7/01/2017	Sat	7	0	0
8/01/2017	Sun	1	0	0
9/01/2017	Mon	2	1	0
10/01/2017	Tue	3	1	1
11/01/2017	Wed	4	1	1
12/01/2017	Thu	5	1	1
13/01/2017	Fri	6	1	1
14/01/2017	Sat	7	0	0
15/01/2017	Sun	1	0	0

[v-caliao-msft]

@eburke,

For your Employee table, do you have a column to show if current employee who has every Monday as an RDO?  If you have a such column, you can use IF function to get the value from Work Days Norm for employees with no RDOs and get value from MondaysOff for employees who have every Monday as an RDO.

https://msdn.microsoft.com/en-us/library/ee634824.aspx

If this is not what you want, please elaborate your requirement, so that we can make further analysis.

Regards,

Charlie Liao

[eburke]

Thank you, I'm still not sure what I need in the employee table to link it to the calender table and show when they have an RDO.  Currently my Emplyee table looks like this:

Provider ID	Advisor %	Hours pd	Calendar
10111922	100%	7.6	FridaysOff
10112490	100%	7.6	Working Days Norm
10113545	100%	7.6	Working Days Norm
10112128	100%	7.6	Working Days Norm
10241789	100%	7.6	Working Days Norm
10116201	100%	7.6	Working Days Norm
10115537	100%	7.6	Working Days Norm
10113847	100%	7.6	Working Days Norm
10114456	100%	7.6	Working Days Norm
10115501	100%	7.6	Working Days Norm
10113322	100%	7.6	Working Days Norm
10112749	100%	7.6	WednesdaysOff
10262115	100%	7.6	Working Days Norm
10113873	100%	7.6	2ndMonday
10131994	100%	7.6	Working Days Norm
10251615	100%	7.6	Working Days Norm
10113238	100%	7.6	2ndThursday
10113689	100%	7.6	Working Days Norm
10132550	100%	7.6	Working Days Norm
10252780	100%	7.6	Working Days Norm
10114178	100%	7.6	Working Days Norm
10111591	100%	7.6	Working Days Norm
10129156	100%	7.6	Working Days Norm
10113413	100%	7.6	FridaysOff
10238025	100%	7.6	WednesdaysOff

How do I link this to my calendar table?  Once linked how do I write the IF function to deal with the different RDOs?  Sorry I'm a bit overwhelmed by it all.  Thanks, Emma.