Employees in the next month and which employees are new or left (also on employee detail level)

[CBO2404]

What I want to see in the 2nd column of my matrix visual: how many employees are there in the next month and later on which employees are new or let.

I want to do this by comparing the population of employees (this month vs next month) because I want to make this visual by department. Some employees change department and I want to see who is new/left in the department.

The first step I try is to count the rows of the next month but I get a blank result. (see example below: When you click on the month you see the employees in detail)

 

You can see the used measure below. When I use the thismonth value to return , return COUNTROWS(distinct(EmployeesOnFirstDateThisMonth)),  I get the same value as in the 1st column so that works fine.

I have used an all filter but that doesn't work.

So what am I doing wrong?

My measure:

EmployeesNextMonth =

      VAR Firstdaythismonth = FIRSTDATE('MyOwndatetable'[date])
      VAR Firstdaynextmonth = FIRSTDATE(NEXTMONTH('MyOwndatetable'[date]))

      VAR EmployeesOnFirstDateThisMonth =
          CALCULATETABLE(VALUES('EmployeeTable'[Employee]),
             FILTER(VALUES('EmployeeTable'[date]),'EmployeeTable'[date] = Firstdaythismonth)
          )

      VAR EmployeesOnFirstDateNextMonth =
          CALCULATETABLE(VALUES('EmployeeTable'[Employee]),
             FILTER(VALUES('EmployeeTable'[date]),'EmployeeTable'[date] = Firstdaynextmonth)
      )

return COUNTROWS(distinct(EmployeesOnFirstDateNextMonth))

--return COUNTROWS(distinct(EmployeesOnFirstDateThisMonth)) THIS RETURNS THE CORRECT VALUE

[CBO2404 · Accepted solution]

This is my solution after trying again and again:

CALCULATETABLE(VALUES('EmployeeTable'[Employee]),
DATESBETWEEN('MyOwndatetable'[date],Firstdaynextmonth,Firstdaynextmonth)

thanks for the suggested solution

[v-yangliu-msft]

Hi  @CBO2404 ,

I created some data:

Here are the steps you can follow：

1. Create calculated column.

YEAR_MONTH = YEAR('Table'[date])&""&MONTH('Table'[date])

Index = RANKX('Table',[date],,ASC,Dense)

Status =
var _lastdeport=CALCULATE(MAX('Table'[deport]),FILTER('Table',[ID]=EARLIER('Table'[ID])&&[Index]=EARLIER('Table'[Index])-1))
return
IF(
    [deport]<>_lastdeport,IF([Index]=1,"Old","New"),"Old")

2. Create measure.

Flag =
IF(
    ISINSCOPE('Table'[ID]),
MIN('Table'[Status]),CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER(ALL('Table'),'Table'[YEAR_MONTH]=MAX('Table'[YEAR_MONTH]))))

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly