topic Re: Need help on calculate new joiner & leaver in DAX Commands and Tips

Need help on calculate new joiner & leaver

Archie_ZHANG — Sun, 11 Feb 2024 07:00:36 GMT

Below is sample table I have, I need two measurement.

First is New joiner, which needs "Current Month Status" = 1, and "Last Month Status"=0, like "Jane Doe".

Second is Leaver, which needs "Current Month Status" = 0, and "Last Month Status"=1 , like "John Smith".

But "Michael Chen" should neither count into New joiner or Leaver, because he is internal promotion.

Employee Name Job Title Status Amount

John Smith	Software Engineer	Last Month Status	1
John Smith	Software Engineer	Current Month Status	0
Jane Doe	Marketing Manager	Last Month Status	0
Jane Doe	Marketing Manager	Current Month Status	1
Michael Chen	Data Analyst 1	Last Month Status	1
Michael Chen	Data Analyst 1	Current Month Status	0
Michael Chen	Data Analyst 2	Last Month Status	0
Michael Chen	Data Analyst 2	Current Month Status	1

Re: Need help on calculate new joiner & leaver

talespin — Sun, 11 Feb 2024 09:49:29 GMT

hi Archie_ZHANG ,

There can be many ways to solve this. You have not covered all scenarios. What if employee is promoted and leaves and joins the company again. Please note the solution may not work properly under unknown scenarios. If you can share all of them, I can cover them.

The logic I have used to identify joiners and leavers is

Take max of Previous month status and Current month Status

PrevMth = 0 and CurrMth = 1 - joiners

PrevMth = 1 and CurrMth = 0 - Leavers

PrevMth = 1 and CurrMth = 1 - Ignore

Count of Joiners =

SUMX(

ALL(TblStat[Employee Name]),

VAR _EmpNme = [Employee Name]

VAR _PrevMth = CALCULATE( MAX(TblStat[Status]), REMOVEFILTERS(TblStat), TblStat[Employee Name] = _EmpNme, TblStat[Check] = "Last Month Status")

VAR _CurrMth = CALCULATE( MAX(TblStat[Status]), REMOVEFILTERS(TblStat), TblStat[Employee Name] = _EmpNme, TblStat[Check] = "Current Month Status")

RETURN IF( _PrevMth = 0 && _CurrMth = 1, 1, 0)

)

Count of Leavers =

SUMX(

ALL(TblStat[Employee Name]),

VAR _EmpNme = [Employee Name]

VAR _PrevMth = CALCULATE( MAX(TblStat[Status]), REMOVEFILTERS(TblStat), TblStat[Employee Name] = _EmpNme, TblStat[Check] = "Last Month Status")

VAR _CurrMth = CALCULATE( MAX(TblStat[Status]), REMOVEFILTERS(TblStat), TblStat[Employee Name] = _EmpNme, TblStat[Check] = "Current Month Status")

RETURN IF( _PrevMth = 1 && _CurrMth = 0, 1, 0)

)

Re: Need help on calculate new joiner & leaver

Archie_ZHANG — Mon, 12 Feb 2024 06:48:28 GMT

talespin Thanks so much for your help. It works well after slight update.

There is an advanced question, not sure can you help to check. Current "Leaver" just count current month & prior month exit employees, do you have any idea on how to get TTM (tailing 12 months, i.e. Feb-23 to Jan-24) leavers and YTD leavers (Jul-23 to Jan-24, Jul is start month of our company). Thanks.

This is HC table raw data: https://drive.google.com/file/d/1PMpP5xwBp6IqtXnrkc9ODEgplfBnw6Sf/view?usp=sharing

**"Calendar" table is time-series table in my PBI working file, which can support Time function.

//this is "leavers" modified from your solution

Leavers =

VAR _prior ='Universal'[Prior] //input 12 in universal variable

VAR _current='Universal'[Current] //input 01 in universal variable

RETURN

SUMX (

DISTINCT ( 'HC EPM'[EE ID] ),

VAR _id = 'HC EPM'[EE ID]

VAR _PrevMth =

CALCULATE (

MAX ( 'HC EPM'[Amount] ),

REMOVEFILTERS ( 'HC EPM' ),

'HC EPM'[EE ID] = _id,

'HC EPM'[Scenario] = "Current Fcst",

'Calendar'[FiscalYear]="FY24",

'Calendar'[Monthnumber]=_prior

)

VAR _CurrMth =

CALCULATE (

MAX ( 'HC EPM'[Amount] ),

REMOVEFILTERS ( 'HC EPM' ),

'HC EPM'[EE ID] = _id,

'HC EPM'[Scenario] = "Current Fcst",

'Calendar'[FiscalYear]="FY24",

'Calendar'[Monthnumber]=_current

)

RETURN

IF ( _PrevMth = 1 && _CurrMth = 0, -1, 0 )

)

Re: Need help on calculate new joiner & leaver

talespin — Mon, 12 Feb 2024 07:16:06 GMT

hi Archie_ZHANG ,

You're welcome. Will check and let you know.

Re: Need help on calculate new joiner & leaver

talespin — Mon, 12 Feb 2024 08:28:52 GMT

hi Archie_ZHANG

Please help understand raw data.

1. For EE_ID = E222446, I see two records for each month with different job title, one with Amount 1 and 0.

2. What is the significance of "column "Scenario".

Re: Need help on calculate new joiner & leaver

Archie_ZHANG — Mon, 12 Feb 2024 09:11:15 GMT

1. Employee with "0" stands for this guy is not in this role,but recoreded due to he on this role before. "1" stands for the role of the employee right now in-seat. For the EE_ID E222446, can understand FY23 whole year, this employee on "SQ_LEARNING ENGAGEMENT AE 4_SQ56014", but he moves to "SQ_LEARNING AE 4_SQ56014" from Jul-24. I also attach a screenshot of original ERP pulling for your better understanding.

2. Scenario is an indicator to distinguish forecast cycle, Current Fcst is the last forecast case finance team process, Plan is planning case finance team doing at the beginning of fiscal year. For easier work, you can filter out "plan" case.

Re: Need help on calculate new joiner & leaver

talespin — Mon, 12 Feb 2024 10:20:54 GMT

hi Archie_ZHANG

Sorry, I do not understand the data. I have lot of questions.

What about this E151492 & E237903. Please explain.

Re: Need help on calculate new joiner & leaver

Archie_ZHANG — Mon, 12 Feb 2024 11:26:02 GMT

talespin Super thanks to your help. E151492, when planning we assume this employee will in-seat across whole year, so "Scenario - Plan" E151492 all month is 1, but this employee resigned at Nov. So from Dec-24 under "Current Fcst", it shows 0 from Dec. E237903, this employee resigned at the end of FY23 (Jun-23), so others month are 0.

If the raw data is too troublesome to understand, you can refer to our original table, can imagine "TblStat[Check]" become three opinions, like, "Current month", "Last Month", and "Two month ago". Then trying to get the rolling leavers number for each month.

Re: Need help on calculate new joiner & leaver

talespin — Tue, 13 Feb 2024 04:15:52 GMT

hi Archie_ZHANG

Used two measures,

Leavers - It will return Leavers for all months.

Rolling 12Mths Leavers - For every month it will Sum for last 12 month.

To show only last 12 months data in Table and Column chart, i have also applied filter Calendar[Date] on visual(Screenshot).

Also sharing data i used.

The logic I have used to identify joiners and leavers is

Take max of Previous month status and Current month Status

PrevMth = 1 and CurrMth = 0 - Leavers

PrevMth = 1 and CurrMth = 1 - Ignore

Leavers =

SUMX(

SUMMARIZE(FactEmployee, 'CALENDAR'[YEAR], 'CALENDAR'[MonthNo], FactEmployee[Employee Name]),

VAR _Year = [YEAR]

VAR _Mth = [MonthNo]

VAR _Dt = DATE(_Year, _Mth, 1)

VAR _CurrMth = CALCULATE( MAX(FactEmployee[Status]), REMOVEFILTERS('CALENDAR'), FILTER( ALL('CALENDAR'[Date]), 'CALENDAR'[Date] = _Dt) )

VAR _PrevMth = CALCULATE( MAX(FactEmployee[Status]), REMOVEFILTERS('CALENDAR'), FILTER( ALL('CALENDAR'[Date]), 'CALENDAR'[Date] = EDATE(_Dt, -1)) )

RETURN IF( _PrevMth = 1 && _CurrMth = 0, 1, 0)

)

Rolling 12Mths Leavers =

VAR _Year = MAX('CALENDAR'[YEAR])

VAR _Mth = MAX('CALENDAR'[MonthNo])

VAR _CurrentDate = DATE(_Year, _Mth, 1)

VAR _T12Date = EOMONTH(_CurrentDate, -12)+1

RETURN CALCULATE([Leavers], 'CALENDAR'[Date] >= _T12Date && 'CALENDAR'[Date] <= _CurrentDate)

Employee NameJob TitleDtCheckStatus

John Smith	Software Engineer	01 October 2022	0
John Smith	Software Engineer	01 November 2022	0
John Smith	Software Engineer	01 December 2022	0
John Smith	Software Engineer	01 January 2023	0
John Smith	Software Engineer	01 February 2023	0
John Smith	Software Engineer	01 March 2023	0
John Smith	Software Engineer	01 April 2023	0
John Smith	Software Engineer	01 May 2023	0
John Smith	Software Engineer	01 June 2023	0
John Smith	Software Engineer	01 July 2023	1
John Smith	Software Engineer	01 August 2023	1
John Smith	Software Engineer	01 September 2023	1
John Smith	Software Engineer	01 October 2023	1
John Smith	Software Engineer	01 November 2023	1
John Smith	Software Engineer	01 December 2023	1
John Smith	Software Engineer	01 January 2024	1
John Smith	Software Engineer	01 February 2024	1
Jane Doe	Marketing Manager	01 October 2022	1
Jane Doe	Marketing Manager	01 November 2022	1
Jane Doe	Marketing Manager	01 December 2022	1
Jane Doe	Marketing Manager	01 January 2023	1
Jane Doe	Marketing Manager	01 February 2023	1
Jane Doe	Marketing Manager	01 March 2023	1
Jane Doe	Marketing Manager	01 April 2023	1
Jane Doe	Marketing Manager	01 May 2023	1
Jane Doe	Marketing Manager	01 June 2023	1
Jane Doe	Marketing Manager	01 July 2023	1
Jane Doe	Marketing Manager	01 August 2023	0
Jane Doe	Marketing Manager	01 September 2023	0
Jane Doe	Marketing Manager	01 October 2023	0
Jane Doe	Marketing Manager	01 November 2023	0
Jane Doe	Marketing Manager	01 December 2023	0
Jane Doe	Marketing Manager	01 January 2024	0
Jane Doe	Marketing Manager	01 February 2024	0
Michael Chen	Data Analyst 1	01 October 2022	1
Michael Chen	Data Analyst 1	01 November 2022	1
Michael Chen	Data Analyst 1	01 December 2022	1
Michael Chen	Data Analyst 1	01 January 2023	1
Michael Chen	Data Analyst 1	01 February 2023	1
Michael Chen	Data Analyst 1	01 March 2023	1
Michael Chen	Data Analyst 1	01 April 2023	1
Michael Chen	Data Analyst 1	01 May 2023	1
Michael Chen	Data Analyst 1	01 June 2023	1
Michael Chen	Data Analyst 1	01 July 2023	1
Michael Chen	Data Analyst 1	01 August 2023	1
Michael Chen	Data Analyst 1	01 September 2023	0
Michael Chen	Data Analyst 1	01 October 2023	0
Michael Chen	Data Analyst 1	01 November 2023	0
Michael Chen	Data Analyst 1	01 December 2023	0
Michael Chen	Data Analyst 1	01 January 2024	0
Michael Chen	Data Analyst 1	01 February 2024	0
Michael Chen	Data Analyst 2	01 October 2022	0
Michael Chen	Data Analyst 2	01 November 2022	0
Michael Chen	Data Analyst 2	01 December 2022	0
Michael Chen	Data Analyst 2	01 January 2023	0
Michael Chen	Data Analyst 2	01 February 2023	0
Michael Chen	Data Analyst 2	01 March 2023	0
Michael Chen	Data Analyst 2	01 April 2023	0
Michael Chen	Data Analyst 2	01 May 2023	0
Michael Chen	Data Analyst 2	01 June 2023	0
Michael Chen	Data Analyst 2	01 July 2023	0
Michael Chen	Data Analyst 2	01 August 2023	0
Michael Chen	Data Analyst 2	01 September 2023	1
Michael Chen	Data Analyst 2	01 October 2023	1
Michael Chen	Data Analyst 2	01 November 2023	1
Michael Chen	Data Analyst 2	01 December 2023	1
Michael Chen	Data Analyst 2	01 January 2024	1
Michael Chen	Data Analyst 2	01 February 2024	1
Mark	Marketing Manager	01 October 2022	1
Mark	Marketing Manager	01 November 2022	1
Mark	Marketing Manager	01 December 2022	1
Mark	Marketing Manager	01 January 2023	1
Mark	Marketing Manager	01 February 2023	1
Mark	Marketing Manager	01 March 2023	1
Mark	Marketing Manager	01 April 2023	1
Mark	Marketing Manager	01 May 2023	1
Mark	Marketing Manager	01 June 2023	1
Mark	Marketing Manager	01 July 2023	1
Mark	Marketing Manager	01 August 2023	1
Mark	Marketing Manager	01 September 2023	1
Mark	Marketing Manager	01 October 2023	1
Mark	Marketing Manager	01 November 2023	0
Mark	Marketing Manager	01 December 2023	0
Mark	Marketing Manager	01 January 2024	0
Mark	Marketing Manager	01 February 2024	0
Rose	Software Engineer	01 October 2022	0
Rose	Software Engineer	01 November 2022	0
Rose	Software Engineer	01 December 2022	0
Rose	Software Engineer	01 January 2023	0
Rose	Software Engineer	01 February 2023	0
Rose	Software Engineer	01 March 2023	0
Rose	Software Engineer	01 April 2023	0
Rose	Software Engineer	01 May 2023	0
Rose	Software Engineer	01 June 2023	0
Rose	Software Engineer	01 July 2023	0
Rose	Software Engineer	01 August 2023	0
Rose	Software Engineer	01 September 2023	0
Rose	Software Engineer	01 October 2023	0
Rose	Software Engineer	01 November 2023	0
Rose	Software Engineer	01 December 2023	1
Rose	Software Engineer	01 January 2024	1
Rose	Software Engineer	01 February 2024	1
Lilly	Data Analyst 1	01 October 2022	0
Lilly	Data Analyst 1	01 November 2022	0
Lilly	Data Analyst 1	01 December 2022	0
Lilly	Data Analyst 1	01 January 2023	0
Lilly	Data Analyst 1	01 February 2023	1
Lilly	Data Analyst 1	01 March 2023	1
Lilly	Data Analyst 1	01 April 2023	1
Lilly	Data Analyst 1	01 May 2023	1
Lilly	Data Analyst 1	01 June 2023	1
Lilly	Data Analyst 1	01 July 2023	1
Lilly	Data Analyst 1	01 August 2023	0
Lilly	Data Analyst 1	01 September 2023	0
Lilly	Data Analyst 1	01 October 2023	0
Lilly	Data Analyst 1	01 November 2023	0
Lilly	Data Analyst 1	01 December 2023	0
Lilly	Data Analyst 1	01 January 2024	0
Lilly	Data Analyst 1	01 February 2024	0