To prepare for this recipe, do the following:
- Open Power BI Desktop.
- Use an Enter Data query to create a table called R02_Table with the following data:
|
Employee |
Hire Date |
Leave Date |
|
Greg |
3/1/2015 |
1/1/9999 |
|
Julie |
12/1/2016 |
1/1/9999 |
|
Scott |
6/1/2017 |
1/1/9999 |
|
Bob |
8/1/2017 |
1/1/9999 |
|
Jimmy |
9/1/2017 |
2/1/2019 |
|
John |
1/1/2018 |
1/1/9999 |
|
Terry |
3/1/2018 |
1/1/9999 |
|
Billy |
1/1/2018 |
12/1/2018 |
|
Sam |
3/1/2019 |
1/1/9999 |
|
Harry |
8/1/2018 |
1/1/9999 |
|
Jerry |
9/1/2019 |
1/1/9999 |
|
Rob |
9/1/2019 |
1/1/9999 |
|
Sajith |
9/1/2019 |
1/1/9999 |
|
Pam |
9/1/2019 |
1/1/9999 |
|
Mike |
9/1/2019 |
1/1/9999 |
|
Aaron |
9/1/2019 |
1/1/9999 |
- Use an Enter Data query to create a table called R02_Absent with the following data:
|
Employee |
AbsentStartDate |
AbsentReturnDate |
|
Jimmy |
1/15/2019 |
1/17/2019 |
|
Jerry |
6/1/2019 |
6/2/2019 |
|
Jerry |
7/1/2019 |
7/3/2019 |
|
Aaron |
3/3/2019 |
3/6/2019 |
|
Aaron |
4/3/2019 |
4/6/2019 |
|
Aaron |
5/3/2019 |
5/6/2019 |
|
Aaron |
6/3/2019 |
6/6/2019 |
|
Aaron |
7/3/2019 |
7/6/2019 |
|
Aaron |
12/28/2019 |
1/4/2020 |
- Relate tables R02_Table and R02_Absent on the Employee columns and ensure that no other relationships exist for the R02_Table and R02_Absent tables. The relationship should be one-to-many, with the one side being R02_Table and the many side being R02_Absent.
- Create a table called R02_Calendar using the following formula:
R02_Calendar = CALENDAR(DATE(2019,1,1), DATE(2019,12,31))
- Ensure that the R02_Calendar table is not related to any other table.
R02_Table represents a typical employee table that contains dates for when the employee was hired by the organization and when the employee left the organization. Many such systems track leave dates by using a nonsensical date such as 1/1/1900 or 1/1/9999 as the Leave Date for employees that are still with the company. Other systems use a blank or null value. The recipe presented here can be easily adjusted to account for these differences between systems.