Forum Discussion
Lookup Table change with time
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).
Anyone? Thank you
- Vvelarde
Community Champion
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
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.
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
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!
