Lookup Table change with time

[Anonymous]

Excuse my title, as I wasnt sure what to name this topic. I have a lookup table that uses an employee number to tell what budget group someone is in. One of the employees here is moving to a different budget group and I am not sure how to have the data reflect that without affecting previous years. I am attaching some screenshots to reference

 The Employee Number cannot change.

[v-juanli-msft]

Hi @Anonymous 

From your screenshot, i'm not clear about which are "budget group", "previous years","employee number".

"One of the employees here is moving to a different budget group (change) and I am not sure how to have the data reflect that without affecting previous years. "

How do you create this lookup table?

(from another table called "source table" here ?

does the "change" occur in "source table"?)

Best Regards

Maggie

[Anonymous]

---

@v-juanli-msft wrote:

Hi @Anonymous 

From your screenshot, i'm not clear about which are "budget group", "previous years","employee number".

"One of the employees here is moving to a different budget group (change) and I am not sure how to have the data reflect that without affecting previous years. "

How do you create this lookup table?

(from another table called "source table" here ?

does the "change" occur in "source table"?)

Best Regards

Maggie

---

I can see how my question was a bit unclear. So my expenses sheet is shown here. The employee number is used ina lookup table to find budget group.

Then we have the original lookup table that I provided. So from 2016-2018 Employee # 13472 was a part of STEER. In 2019 they will be moving to Training H. It is not possible to change their employee number because of how the data is reported. I am trying to find a way to make this changes while ensuring that they are still in the correct budget group for 2016-2018 (STEER).

[Anonymous]

Anyone? Thank you

[Vvelarde]

@Anonymous 

Hi, my suggestion will be in your Budget Table insert Dates / Years .

Example :

Year  Employee  Group 

2015   John          B

2016   John          C

2017 John         C

2018  John       D

With this you can calculate without problems.

Regards

Victor

[Anonymous]

This is definitely a plausible answer; however, this would create a very large lookup table.  Especially as time goes on. I also need this accurate by the month. If there really is no other way I can definitely do it like that. But still looking for a solution that requires a bit less expanding.

I think I worded this kind of poorly initially. So allow me to restate my problem.

@Vvelarde 

I have a lookup table set up like this

Employee ID    Employee Name   Budget Group      Job title

1                       John                     Training                Trainer

2                       Mary                     Management       Manager

3                       Greenberg            Management       Assistant

Employee ID's and Employee Names do not change. How can I make this Dynamic so that I can change someones info without affecting data where they are under a previous job title & Budget group?

The source data is formatted like this using a folder that updates monthly

Date        Cost Element      Employee ID     Cost

x              x                          x                       x

x              x                          x                       x

x              x                          x                       x

So for Example, John is going to move into management into Manager. But this goes into effect in March of 2019. I can't simply add a new selection like shown below because that will make it a many-to-many relationship. I also cannot change his budget group and job title because that will misrepresent his spending for previous years as a trainer and place that money spent on the manager budget when it shouldn't be. How may I approach this?

Employee ID    Employee Name   Budget Group      Job title

1                       John                     Training                Trainer

1                       John                     Management        Manager

2                       Mary                     Management       Manager

3                       Greenberg            Management       Assistant

Thank you,

Michael

[Anonymous]

I'm going to bump this up because I am still trying to work up a viable solution. The propblem is that I need an empoyees information to be accurate by the month.  I am not proficient with DAX and when I went over this with Parry2k he said that is what I'll need. Looking for thoughts or ideas on how to proceed. Thank you!

[Anonymous]

Bump

[ChrisMendoza]

@Anonymous -

Does your organization not utilize an additional Position_Code or similar to make a unique key on EmplID?

0001 is John's EmplID

00010 is John's "1st position"

00011 is John's "2nd position"

If not, you could create this artificially then flag 00010 as inactive retaining those records and 00011 as active.

My organization does identify EmplID & Position_Code; this is my Key that is used for uniqueness.

[Anonymous]

[Anonymous]

Anyone?