Forum Discussion

eburke's avatar
eburke
Icon for Helper II rankHelper II
9 years ago

Calculating work days with different RDOs

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

 

DateDay NameWeekdayWorking Days NormMondaysOff
1/01/2017Sun100
2/01/2017Mon200
3/01/2017Tue311
4/01/2017Wed411
5/01/2017Thu511
6/01/2017Fri611
7/01/2017Sat700
8/01/2017Sun100
9/01/2017Mon210
10/01/2017Tue311
11/01/2017Wed411
12/01/2017Thu511
13/01/2017Fri611
14/01/2017Sat700
15/01/2017Sun100
Need Help

2 Replies

  • v-caliao-msft's avatar
    v-caliao-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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's avatar
      eburke
      Icon for Helper II rankHelper II
      9 years ago

      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 IDAdvisor %Hours pdCalendar
      10111922100%7.6FridaysOff
      10112490100%7.6Working Days Norm
      10113545100%7.6Working Days Norm
      10112128100%7.6Working Days Norm
      10241789100%7.6Working Days Norm
      10116201100%7.6Working Days Norm
      10115537100%7.6Working Days Norm
      10113847100%7.6Working Days Norm
      10114456100%7.6Working Days Norm
      10115501100%7.6Working Days Norm
      10113322100%7.6Working Days Norm
      10112749100%7.6WednesdaysOff
      10262115100%7.6Working Days Norm
      10113873100%7.62ndMonday
      10131994100%7.6Working Days Norm
      10251615100%7.6Working Days Norm
      10113238100%7.62ndThursday
      10113689100%7.6Working Days Norm
      10132550100%7.6Working Days Norm
      10252780100%7.6Working Days Norm
      10114178100%7.6Working Days Norm
      10111591100%7.6Working Days Norm
      10129156100%7.6Working Days Norm
      10113413100%7.6FridaysOff
      10238025100%7.6WednesdaysOff

       

      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.