To prepare for this recipe, do the following:
- Open Power BI Desktop.
- Import the data from the Ch08R01Data.xlsx Excel file located in this book's GitHub repository. Ensure that the table that's created is called R01_Table.
- Create a table called R01_Calendar using the following formula:
R01_Calendar = CALENDAR(DATE(2019,1,1),DATE(2019,3,31))
- Create the following column in the R01_Calendar table:
Work Hours = IF(WEEKDAY('R01_Calendar'[Date],2) < 6,8,0)
- Create a relationship between the Date columns in the R01_Table and R01_Calendar tables and ensure that Cross filter direction is set to Both.
Each row in the R01_Table table represents daily hours reported by employees against various projects and tasks within those projects. Each project has a unique JobID that identifies the project and each task has a TaskID that identifies the task within the project. In addition, each employee has a PayType that identifies the employee as either an internal resource (ADMINISTRATION) or as some form of billable employee (SALARY, HOURLY, SUB-CONTRACTOR). Finally, each combination of project and task code is given a Category of either Billable or a non-billable category such as Bench, Int Admin, or PTO.
The Work Hours column in the R01_Calendar table simply determines if the Date in each row is a weekday or a weekend. If the Date is a weekday, a value of 8 hours is assigned; otherwise, a value of 0 hours is assigned. Thus, the Work Hours column represents the total number of potential billable hours available for each day.