Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi, I'm looking to beef up a report counting EmployeesAtDate and EmployeesAt1YearPrior with a retained employees since 1YearAgo column. So far my Excel PowerPivot PivotTable is:
Row LabelsEmployeesAtDateEmployeesAt1YearPrior
1/1/2019 | 189 | |
2/1/2019 | 199 | |
3/1/2019 | 203 | |
4/1/2019 | 207 | |
5/1/2019 | 217 | |
6/1/2019 | 225 | |
7/1/2019 | 227 | |
8/1/2019 | 237 | |
9/1/2019 | 243 | |
10/1/2019 | 247 | |
11/1/2019 | 239 | |
12/1/2019 | 234 | |
1/1/2020 | 231 | 189 |
2/1/2020 | 235 | 199 |
3/1/2020 | 228 | 203 |
4/1/2020 | 227 | 207 |
5/1/2020 | 236 | 217 |
6/1/2020 | 233 | 225 |
7/1/2020 | 233 | 227 |
8/1/2020 | 221 | 237 |
9/1/2020 | 223 | 243 |
10/1/2020 | 215 | 247 |
11/1/2020 | 218 | 239 |
12/1/2020 | 213 | 234 |
1/1/2021 | 209 | 231 |
2/1/2021 | 200 | 235 |
3/1/2021 | 204 | 228 |
4/1/2021 | 201 | 227 |
5/1/2021 | 199 | 236 |
6/1/2021 | 185 | 233 |
7/1/2021 | 187 | 233 |
8/1/2021 | 194 | 221 |
There's two pretty straightforward tables involved here.
tblCalendar = {"1/1/2019"..."8/1/2021"} - the first date of each month in the period of interest
qryEmployeeActiveTime = a set of rows made up of an employee ID and a date for every date they are active. For instance, if Alice was active from 1/1/2020 thru 1/3/2020, there would be the following rows in the table:
Employee | TenureDate |
Alice | 1/1/2020 |
Alice | 1/2/2020 |
Alice | 1/3/2020 |
In the PivotTable on the top, "EmployeesAtDate" is a count of all rows where TenureDate matches the tblCalendar[Date] of the row given. The "EmployeesAt1YearPrior" is a count of all rows where the TenureDate matches DATEADD(tblCalendar[Date],-1,YEAR) of the row given.
What I need to do for the retained employees is get a count of all employees who are active at the tblCalendar[Date] of the row given, who were also active at DATEADD(tblCalendar[Date],-1,YEAR). I'm throwing crap at the wall to see what sticks and not coming up with much. Any pointers?
@LaBicicleta , What is you source data, where we can see Alice's start and end date.
If you have a start and end date. Refer to the file attached, which can help
Hi @amitchandak , thanks for reaching out!
The issue is that we have a lot of rehires here, so there's multiple periods of tenure that need considered, I can't just grab the current "End Date" from the employee table if they're a rehire.
However, what I can do is use PowerQuery to generate a table that would have one row for each employee-tenure period, a la:
Row ID | Employee ID | Tenure_Start | Tenure_End |
1 | Alice | 3/1/2018 | 8/20/2019 |
2 | Alice | 10/31/2019 | 12/31/2049 |
3 | Bob | 7/1/2021 | 12/31/2049 |
I can certainly try this out.
I did try to pursue a solution with my existing two tables in the original post, and I came up with this English explanation of what I envision as the solution:
My attempted solution was this:
=COUNTROWS(
FILTER(
SUMMARIZE(
FILTER(qryEmployeeActiveTime,
OR(
[TenureDate]=DATEADD(tblCalendar[Date],0,YEAR),
[TenureDate]=DATEADD(tblCalendar[Date],-1,YEAR)
)
),
[EmployeeID],
"TenureInstancesOfInterest",COUNT([EmployeeID])
),
[TenureInstancesOfInterest]=2
)
)
User | Count |
---|---|
53 | |
22 | |
18 | |
18 | |
13 |
User | Count |
---|---|
89 | |
87 | |
50 | |
34 | |
22 |