cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jjkmd
Helper I
Helper I

Manage changing job classifications over time.

Paycor is our payroll service, and we can output a report that has information on each employee for each payroll date.

 

At it's most basic it will show

Payroll Date : Name  : Job Title :  Pay for that period of time

 

Job title is the issue.  If an employee gets a changed job title, then all past transactions are changed to that new job title if I run the report today.  So, if 3 of our employees changed from Reception to MA as of July 2017, and are now classified as MA when I run the report today, and I want to see what our Receptionist department cost was in June 2017, then those 3 will be missing from the list because they are now classified as MA from now back to the beginning.

 

I'm sure there exists some system or clever solution to manage this, i'm just not sure what it is or where to start.  An suggestions?

 

 

2 REPLIES 2
Anonymous
Not applicable

Well, should you then not keep a history of Job Title changes for each and every Name? You should.

So, you have to have another fact table that will store job titles for all dates and all employees. Connect this to your Dates table.

By the way, you need the following dimensions: Dates, Employees (for Names), JobTitles. And two fact tables: Payroll (Date,Name,Pay), JobTitleHistory (Date,Name,JobTitle).

Join dimensions to facts with 1:many relationships and you're OK to build your DAX.

I'd also suggest not to use Name as the id field but EmployeeID (which will not change even if the employee's name does).

The key to building good and easy to manage models is to reach the star schema. The above will give you one.

Best
Darek

So, i'm creating and maintaining a table like this?

 

 

Employee numberEmployee nameDatePosition
1John Doe1/1/2019Clerk
2Peter Parker1/1/2019Clerk
3Daisy May1/1/2019Reception
4Lexy Ann1/1/2019Reception
5Jack Black1/1/2019Manager
1John Doe1/2/2019Clerk
2Peter Parker1/2/2019Clerk
3Daisy May1/2/2019Reception
4Lexy Ann1/2/2019Reception
5Jack Black1/2/2019Manager
1John Doe1/3/2019Clerk
2Peter Parker1/3/2019Clerk
3Daisy May1/3/2019Reception
4Lexy Ann1/3/2019Reception
5Jack Black1/3/2019Manager
1John Doe1/4/2019Clerk
2Peter Parker1/4/2019Clerk
3Daisy May1/4/2019Reception
4Lexy Ann1/4/2019Reception
5Jack Black1/4/2019Manager
1John Doe1/5/2019Clerk
2Peter Parker1/5/2019Clerk
3Daisy May1/5/2019Reception
4Lexy Ann1/5/2019Reception
5Jack Black1/5/2019Manager
1John Doe1/6/2019Clerk
2Peter Parker1/6/2019Clerk
3Daisy May1/6/2019Reception
4Lexy Ann1/6/2019Reception
5Jack Black1/6/2019Manager

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors