HR Data - Monthly Files

[dgkallan]

Let me lead by saying that I hope this isn't a dumb question.  This is more of a conceptual "how to handle" than a specific "what's the code" type of question.

I'm about to start some work where every month I will receive an updated HR employee data file.  Some people will terminate, new people will be hired, jobs will change, etc.  I'm going to use one measure as an example where I'm worried I won't be able to report historicals.  For example, turnover rate where this is roughly defined as people that left (for certain reasons) divided by the number of active employees.  I'm sure I can calculate this for a given file, but I"m worried that when the next file comes in I won't be able to know what last month's attrition rate was vs. what the new month's is and so on since's it just a new version of this data.  

I hope I'm explaining this correctly and perhaps I'm not seeing this clearly, but any guidance will help.

[dgkallan]

I'm thinking this might help explain. 

Let's say I get this for Sept

Employee	Status	Term Dt
11111	Inactive	9/1/2023
22222	Active
33333	Active

September turnover rate is 33# (1/3)

Then in October I get an update file.  I don't want to count the September turnover in there, so the October turnover is 25% (1/4).  Since I'm getting update files, I don't know how to preserve (other than using the turnover date somehow) the turnover rate by month.

Employee	Status	Term Dt
11111	Inactive	9/1/2023
22222	Inactive	10/1/2023
33333	Active
44444	Active
55555	Active

[FreemanZ]

hi @dgkallan ,

In similar situation, i handle it like:

1) In each monthly table, add a ReportDate column, like 2023/1/1 for 2023-Jan table.

2) Append all the monthly table.

3) Do the analysis on the ReportDate column or a related date column from a dedicated calendar table.

[dgkallan]

Thank you so much.  I was thinking something like this might be the answer.  Do you know of a way to automatically pull the file create date into a Report Date column so that it doesn't require manual manipulation?

And I wanted to use the solution where the new file is just dropped into the same folder every month.  Would that be how the append solution you suggested would work?  Or is there something different that has to be done to append it?

[FreemanZ]

hi @dgkallan ,

It would be possible if your file name include date info, otherwise adding column might be necessary. 

If you familiar with Power Query code, you can duplicate queries and change the report date in advance editor easily.