Data Correlation

[user900]

Hoping someone can help me.  I am currently connected to a large data file with 12 months of data (source 1).  This file includes user name, emp ID and test results.  I created a line chart to show the number tested (emp ID) and measures to show test results for passed vs failed.

Now, I have another user data file (source 2) that shows a complete list of everyone currently in the organization.  I get a new one every month.  If a user from source 1 is not in source 2, the person has left the company.  

In source 1, I created a new column to get which users are in source 2 and called it Active User

Active User = LOOKUPVALUE(Source2[User],Source 2[Emp ID],Source 1[Emp ID])

When I drag this new field into my filters on the visual, it filters all the data, not just the current month.  I need the historical results to be retained to reflect those that were active at that point in time.  How can I use both sources and the new month data source to determine who was active in each given month?  I'm thinking I need to import the new data source each month and change my Active User column to lookup if they are active against the same month's data, but not sure if this is the best way or how to do that.

Please keep in mind that I am not an advanced user. Any help would be appreciated.  Thanks,

~user 900

[hashtag_pete · Accepted solution]

Hello @user900 

if I understand correct, you have the following solution:

Source 1		Source 2	Source 3	Source 4
Emp-ID		Month 1	Month 2	Month3
A		A	A	B
B		B	B	C
C		C	C	D
D			D

What is your source for the month? An excel file? 

My approach would be to add a date column to the monthly source files and to append those files. Then you can make a Date table and select here which month you want to see. If you concatenate Emp-ID with date, you have an identifier.

Does that make sense?

Best

hashtag_pete