Counting employees previous periods

[Anonymous]

Hi,

I have a case in which I have a fact tables containing Employees, their job title a a row per each of the days that I have been in the company.

Something like this.

I want to count employees that are currently in the company and that were already in the company a year ago  and also those who were in the company a year ago BUT (and here comes the difficult par for me) they should be grouped in the current job title.

So the expected result is somthing like this:

I was able to calculate the ones who are still in, but have some problems with the ones that left taking into account that they should appear in the job title they had on the date we are checking.

Another condition is that the measure should work in any day of the year, not only using today as current date.

Anyone knows how to do this? if this is even feasible?  

[amitchandak]

@Anonymous , Create these measures, with help from date table 

This = calculate(Countrows(Table) , filter(allselected(Table), Table[id] = max(Table[ID])))

Last = calculate(calculate(countrows(Table), dateadd('Date'[Date], -1, Year)), filter(allselected(Table), Table[id] = max(Table[ID])))

leaver = countx(values(Table[ID]) , if( not(isblank(Last)) && isblank(This) , [ID] , blank()))

stiillin = countx(values(Table[ID]) , if( not(isblank(Last)) && not(isblank(this)), [ID] , blank()))

[Anonymous]

Hi @amitchandak ,

Thanks for your answer and sorry for the delay. This didn't work, but maybe it's because I didn't explain myself properly.

The idea is to establish a date in a filter, and use the max date as a reference. In the example this max date is 18Nov2021, so in that case, employee 4, for instance, should be counted in D as a leaver, and it shouldn't be in.

This is the output I got with your calculations.

In the case of Job A, for instance, we have employee 1 that is still in the company and that remains on A, so should be counted as 1 on A. Employee 2 started on A and remains in the company but with a different JobTitle today (B), so should be counted as 1 but on Job B.

Basically, they should be counted as StillIn, if they were in the company on 18Nov2020 no matter the JobTitle they had, but they should be assigned to the JobTitle they have nowadays.

In the case of Leave, they can leave a JobTitle, but if they remain in the company (with another title) they shouldn't be counted.

Thanks anyway for your effort!!

Pbix Sample File 

[Anonymous · Accepted solution]

Hi, I finally solved the issue and just wanted to share the result.

Probably is not the most optimal way of doing this, but it's working.

This is for Leavers (only taking into account those who were in the company a year ago)

Leavers = 
--Get the Date to use as reference from the selected period
Var MaxDate = MAX(DimCalendar[Date])
--Get the Date a year before this reference date
Var YearAgoDate = DATE(Year(MaxDate)-1,Month(MaxDate),Day(MaxDate))
--Get latest version of each employee and build a new key (Employee+MaxDate)
Var LastVersion = SUMMARIZE(FILTER(ALL(DimEmployees),DimEmployees[Date]>YearAgoDate && DimEmployees[Date]<MaxDate),
                    DimEmployees[Id],
                    "IdDate",CONCATENATE(DimEmployees[Id],FORMAT(Max(DimEmployees[Date]),"yyyyMMdd")))
--ListEmployees that are currently in the company
Var EmployeesIn = DISTINCT(SELECTCOLUMNS(FILTER(ALL(DimEmployees),DimEmployees[Date]=MaxDate),"Id",DimEmployees[Id]))  
--Complete table with key (employee+date                  
Var ConcatValues =ADDCOLUMNS(SELECTCOLUMNS(DimEmployees,
                    "Id",DimEmployees[Id],
                    "Date",DimEmployees[Date],
                    "JobTitle",DimEmployees[Job]),
                    "IdDate",CONCATENATE([Id],FORMAT([Date],"yyyyMMdd")))
                    
--Final table with all the list, excluding those employees who are still in the company(EmployeesIn) and those rows who are not the last version (LastVersion)
Var FinalTable = SELECTCOLUMNS(FILTER(ConcatValues,NOT [Id] IN (EmployeesIn) && [IdDate] IN (SELECTCOLUMNS(LastVersion,"Id",[IdDate]))) ,
                "Id",[Id],
                    "Date",[Date],"IdDate",[IdDate],"JobTitle",[JobTitle])
Var CountLeavers = COUNTROWS(FinalTable)
RETURN
CountLeavers

 And this is for employees still in (only taking into account those who were here a year ago)

StillIn = 
--Get the Date to use as reference from the selected period
Var MaxDate = MAX(DimCalendar[Date])
--Get the Date a year before this reference date
Var YearAgoDate = DATE(Year(MaxDate)-1,Month(MaxDate),Day(MaxDate))
--List of employees that were in the company a year ago
Var LastYearList = SELECTCOLUMNS(FILTER(ALL(DimEmployees),DimEmployees[Date]=YearAgoDate),
                                "Id",DimEmployees[Id])
--List of employees that are in the company now and were also a year ago                            
Var ThisYearList = SELECTCOLUMNS(FILTER(DimEmployees,DimEmployees[Date]=MaxDate),
                                "Id",DimEmployees[Id])
Var StillInList = COUNTROWS(FILTER(ThisYearList,[Id] in SELECTCOLUMNS(LastYearList,"Id",[Id])))
RETURN
StillInList

 Javi