Hello
I am currently using power pivot for calculate employee turnover. I currently have the employee calculations for each month in a separate worksheet, however I would like to add to the pivot table. I have tried to work out the measure that I would need to add for this to happen each month.
The current calculation that I am using is Terminated employees/ average(EmpCount, Active Employees Total) as this is quite a big spreadsheet I have attached print screen of the pivot table and the current measures that I have in use.
I am using excel 365
Your help with this would be greatly appreciated 🙂
Could you please upload an Excel file with example data, though with the same structure (table name, column names etc.)
So much easier to create and test measures with some realistic data.
Hello
I have created a sample file, hopefully this works for you.
Thank you
Sheralee
To avoid connection errors upon a Refresh, I copied the data from your DM to an Excel table and used that one to build a new DM from with measures that do what you ask for. I trust you can apply similar measures to your real data. If not, come back here.
By the way, when working with calculations comparing current periods with previous periods you can add a calendar table to the DM and use many of the built-in time intelligence functions such as PREVIOUSMONTH, PREVIOUSQUARTER and PREVIOURYEAR. In your model, if I understood it correctly, you don't need it is your beginning head count for a month is in the column called 'Current' and closing head count is the 'Active' column.
