Solved

PowerPivot - Count the Values that were in a Previous Period but Not in Current Period

Posted on 2014-12-18
3
118 Views
Last Modified: 2014-12-25
I'm working to calculate a Turnover Report. I've got an export of Employee ID's by week and would like to count the number of Employees ID's that did not return from working the previous week.

Basically Count the entries that didn't reoccur.

I've attached a basic version of the file I've started with the data loaded into PowerPivot via a Linked Table along with a Calendar Table.

If you look at the data an example would be that EmployeeID 1158 worked on 11/2/2014, but didn't work on 11/9/2014. I want to be able to count all the various EmployeeID's where this is the case. So the count of Employees that worked in the previous week, but not in the current week. In this example the count would be under Week 11/9/2014.
0
Comment
Question by:thomas-sherrouse
3 Comments
 
LVL 23

Expert Comment

by:Eirman
Comment Utility
Try again with the attachment.
0
 

Author Comment

by:thomas-sherrouse
Comment Utility
Sorry File attached
TurnoverExample-PowerPivot.xlsx
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
Comment Utility
You could create a new calculated column to show the employee id only for rows where the employee didn't work the following week:
=if(isblank(countrows(filter(EmployeeList,EmployeeList[EmployeeID]=earlier(EmployeeList[EmployeeID])&&EmployeeList[WeekWorked]=earlier(EmployeeList[Next Week])))),EmployeeList[EmployeeID],blank())

then you would need to use your 'Next week' column as the date grouping and count the distinct rows for the new column.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
Creating and Managing Databases with phpMyAdmin in cPanel.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now