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 |
Annual Salary |
|
Greg |
3/1/2015 |
1/1/9999 |
100000 |
|
Julie |
12/1/2016 |
1/1/9999 |
150000 |
|
Scott |
6/1/2017 |
1/1/9999 |
120000 |
|
Bob |
8/1/2017 |
1/1/9999 |
75000 |
|
Jimmy |
9/1/2017 |
2/1/2019 |
45000 |
|
John |
1/1/2018 |
1/1/9999 |
110000 |
|
Terry |
3/1/2018 |
1/1/9999 |
90000 |
|
Billy |
1/1/2018 |
12/1/2018 |
80000 |
|
Sam |
3/1/2019 |
1/1/9999 |
70000 |
|
Harry |
8/1/2018 |
1/1/9999 |
60000 |
|
Jerry |
9/1/2019 |
1/1/9999 |
100000 |
|
Rob |
9/1/2019 |
1/1/9999 |
150000 |
|
Sajith |
9/1/2019 |
1/1/9999 |
90000 |
|
Pam |
9/1/2019 |
1/1/9999 |
100000 |
|
Mike |
9/1/2019 |
1/1/9999 |
110000 |
|
Aaron |
9/1/2019 |
1/1/9999 |
80000 |
- Create a table called R04_Calendar using the following formula:
R04_Calendar = CALENDAR(DATE(2019,1,1), DATE(2019,12,31))
- Ensure that both the R04_Table table and the R04_Calendar table do not have any relationships with any other tables, including each other.
This data 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.