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 |
This data represents a typical employee table that contains dates for when an employee was hired by the organization and when they 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.