Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
JCasson
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 IDFTEYear MonthPrevious month FTEMarker
10000112019 JanNANA
1000020.72019 JanNANA
1000030.82019 JanNANA
10000412019 JanNANA
10000512019 JanNANA
10000112019 Feb1 
1000020.72019 Feb0.7Leaver
1000030.82019 Feb0.8 
10000412019 Feb1 
10000512019 Feb1 
10000112019 Mar1 
1000030.82019 Mar0.8 
10000412019 Mar1 
10000512019 Mar1 
10000612019 Mar0Joiner
1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

@JCasson 

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())))

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

7 REPLIES 7
ryan_mayu
Super User
Super User

@JCasson 

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())))

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@ryan_mayu :' I came across this reply from you from 2020 and wanted to thank you as I am facing a similar issue and your solution is just fantastic!

 

Could I ask if I wanted to Previous Month FTE for a new joiner as BLANK since that's more accurate, what change should I do?

return if('Table'[Year Month]=mindate,blank(),if(ISBLANK(previous),0,previous))

I tried replacing zero above with BLANK() but that's messed up joiners in the joiners/leavers column.

 

Secondly, and more importantly, do you have suggestions to modify this so that it can account for scenarios where a person joins in a month and quits before the next so that they are effectively a joiner and leaver in the current month column?

 

In any case, appreciate your work!!

could you pls provide the expected output based on the sample data in the first post?

if the sample data is not good, pls provide a new one and also provide your expected output. 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thanks for responding😀

 

I have made a new sample data set since the original doesn't cover this scenario.

Employee IDFTEDate
12312023-01
23202023-01
3330.92023-01
12312023-02
55512023-02
23212023-02
44402023-02
12312023-03
23212023-03
44412023-03

 

What's different in the above data set from OP's original in the question is the presence of 555 who appears only for a month and hence is both a joiner and leaver.

 

 

Resulting Output:

Employee IDFTEDatePrev. FTEJoiner/Leaver
12312023-01  
23202023-01  
3330.92023-01 Leaver
12312023-021 
55512023-02 Joiner+Leaver
23212023-020 
44402023-02 Joiner
12312023-031 
23212023-031 
44412023-030 

 

 

Hope this make sense. My point being that with an actual data set there could be an FTE that appears just for one month and leaves and is both a joiner and leaver.

 

Also, for a new joiner it makes sense to have Prev. FTE as BLANK instead of zero (like in your original solution)

@zb134 

you can try this

Column = 
var _min=min('Table'[Date])
var _max=max('Table'[Date])
var _last=maxx(FILTER('Table','Table'[Employee ID]=EARLIER('Table'[Employee ID])&&'Table'[Date]<EARLIER('Table'[Date])),'Table'[Date])
var _next=minx(FILTER('Table','Table'[Employee ID]=EARLIER('Table'[Employee ID])&&'Table'[Date]>EARLIER('Table'[Date])),'Table'[Date])
var _count=countx(FILTER('Table','Table'[Employee ID]=EARLIER('Table'[Employee ID])),'Table'[Employee ID])
return if('Table'[Date]<>_max&&'Table'[Date]<>_min&&_count=1,"joiner+leaver",if('Table'[Date]<>_max && ISBLANK(_next),"leave",if('Table'[Date]<>_min&&ISBLANK(_last),"joiner")))

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thanks this is perfect! 

you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.