cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Add column to show joiners and leavers

Dataset table has employee IDs and their full-time equivalent values by months spanning two years in a column. How do I calculate who are new joiners and who are leavers, i.e. to populate columns "Previous month FTE" and "Marker"?

I tried to calculate first previous month FTE using "calculate (sum(FTE), previousmonth (Year Month)", but failed. essentially if I can get the previous month FTE, then the Marker would be a if function of: If previous month FTE is not found, mark as "Joiner", and if previous month FTE is >0 and FTE is not found, then mark as "Leaver".

 Employee ID FTE Year Month Previous month FTE Marker 100001 1 2019 Jan NA NA 100002 0.7 2019 Jan NA NA 100003 0.8 2019 Jan NA NA 100004 1 2019 Jan NA NA 100005 1 2019 Jan NA NA 100001 1 2019 Feb 1 100002 0.7 2019 Feb 0.7 Leaver 100003 0.8 2019 Feb 0.8 100004 1 2019 Feb 1 100005 1 2019 Feb 1 100001 1 2019 Mar 1 100003 0.8 2019 Mar 0.8 100004 1 2019 Mar 1 100005 1 2019 Mar 1 100006 1 2019 Mar 0 Joiner
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II

## Re: Add column to show joiners and leavers

you can try to create two columns

``````Column =
VAR mindate=minx(all('Table'),'Table'[Year Month])
VAR previous=MAXX(FILTER('Table','Table'[Employee ID]=EARLIER('Table'[Employee ID])&&'Table'[Year Month]<EARLIER('Table'[Year Month])),'Table'[FTE])
return if('Table'[Year Month]=mindate,blank(),if(ISBLANK(previous),0,previous))

Column 2 =
VAR mindate=minx(all('Table'),'Table'[Year Month])
VAR maxdate=MAXX(all('Table'),'Table'[Year Month])
VAR previous=MAXX(FILTER('Table','Table'[Employee ID]=EARLIER('Table'[Employee ID])&&'Table'[Year Month]<EARLIER('Table'[Year Month])),'Table'[FTE])
VAR next=MINX(FILTER('Table','Table'[Employee ID]=EARLIER('Table'[Employee ID])&&'Table'[Year Month]>EARLIER('Table'[Year Month])),'Table'[FTE])
return if(ISBLANK('Table'[Column]),BLANK(),if(ISBLANK(previous),"joiner",if('Table'[Column]>0&&ISBLANK(next)&&'Table'[Year Month]<>maxdate,"leaver",blank())))``````

Proud a to be a Datanaut!
Thanks and BR
Ryan
Highlighted
Super User II

## Re: Add column to show joiners and leavers

you can try to create two columns

``````Column =
VAR mindate=minx(all('Table'),'Table'[Year Month])
VAR previous=MAXX(FILTER('Table','Table'[Employee ID]=EARLIER('Table'[Employee ID])&&'Table'[Year Month]<EARLIER('Table'[Year Month])),'Table'[FTE])
return if('Table'[Year Month]=mindate,blank(),if(ISBLANK(previous),0,previous))

Column 2 =
VAR mindate=minx(all('Table'),'Table'[Year Month])
VAR maxdate=MAXX(all('Table'),'Table'[Year Month])
VAR previous=MAXX(FILTER('Table','Table'[Employee ID]=EARLIER('Table'[Employee ID])&&'Table'[Year Month]<EARLIER('Table'[Year Month])),'Table'[FTE])
VAR next=MINX(FILTER('Table','Table'[Employee ID]=EARLIER('Table'[Employee ID])&&'Table'[Year Month]>EARLIER('Table'[Year Month])),'Table'[FTE])
return if(ISBLANK('Table'[Column]),BLANK(),if(ISBLANK(previous),"joiner",if('Table'[Column]>0&&ISBLANK(next)&&'Table'[Year Month]<>maxdate,"leaver",blank())))``````

Proud a to be a Datanaut!
Thanks and BR
Ryan

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors