Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to Solution.
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 to be a Super User!
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 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.
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 ID | FTE | Date |
123 | 1 | 2023-01 |
232 | 0 | 2023-01 |
333 | 0.9 | 2023-01 |
123 | 1 | 2023-02 |
555 | 1 | 2023-02 |
232 | 1 | 2023-02 |
444 | 0 | 2023-02 |
123 | 1 | 2023-03 |
232 | 1 | 2023-03 |
444 | 1 | 2023-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 ID | FTE | Date | Prev. FTE | Joiner/Leaver |
123 | 1 | 2023-01 | ||
232 | 0 | 2023-01 | ||
333 | 0.9 | 2023-01 | Leaver | |
123 | 1 | 2023-02 | 1 | |
555 | 1 | 2023-02 | Joiner+Leaver | |
232 | 1 | 2023-02 | 0 | |
444 | 0 | 2023-02 | Joiner | |
123 | 1 | 2023-03 | 1 | |
232 | 1 | 2023-03 | 1 | |
444 | 1 | 2023-03 | 0 |
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)
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")))
Proud to be a Super User!
Thanks this is perfect!
you are welcome
Proud to be a Super User!
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |