I am currently working on some headcount reporting to show headcount Month over Month. One of the metrics assigned is to show resources that were in the prior month but no longer there in the current month.   I have all the other metrics set up and ready to go - however am unable to get this one to be viewed correctly. 

Does anyone know a way around this?

@Ryan Greene, @Jenny Franklin, @Gregg Palaian, @David Glass

Adding @Jenny Goodwin and @Debbie Hagen

Let me see what I can figure out, but if anyone else who happens to be following me and sees this post feel free to jump in with an answer. @Jaiveer Kahlon, are you trying to do this in a KPI?  Table?  

The solution depends on what your trying to display and what data you have access to.

Ideally you'd just have a file that showed terminations and another that showed additions and report on that.

If you don't have this data, then you need a way to determine it. The two ways I suggest are

1.)Load the same resource file twice to different tables offset by 1 month (datalink connector that runs twice), add a column to each, say "Current Month"="1" and Prior Month ="1".  From here, you can append them together, then group on the employee name, then sum the columns. If the sum is 2, you know they are present for both months, if not then, you know they were added or removed.

2.)Similar to above, except you need to append the data together via datalink (Every month appends to prior month)  From here you make 2 transforms off the data, and in one, you add or subtract a month from the date column to offset the data by a month, then date filter, and then append them together and use the logic I outlined in the above step.

If you just care about a count, you can model the number in a model metric (headcount). Then on the reporting surface do Headcount - timeperiod(Headcount,-1) that will show you the difference in total headcount, but if you lose 5 resources and gain 5 resources, it's not really telling the actual story.

The last way, and what I think is the most useful is to simply have 1 file, model it with headcount, then have a report that trends the name/resource ID over some number of months (say current and prior) add a total column, and filter where it !=0. then you can see your adds and deletes in one table.

Another option: Lock a report table to the previous time period (instead of the usual current time period), using Properties > Advanced > Time Lock.

In this table, show Labor resources by employee ID and a simple count (such as "1" per row). 

Add logic which uses TimePeriod() to look forward 1 month (from previous month into current month) to check the count.

Filter the table to show only rows where the detected future count is zero.

The remaining rows show the employees who were present last month but are not present this month.

The opposite strategy can show opposite results (employees new this month).

Below is a similar example using server data instead of labor data. 

Thanks so much @Mark Nealey and @Chris Davidson for jumping in and helping with this question!

Thanks so much! Appreciate the assistance!

Asurion_Internal_Use_Only