Problem: I need to identify the year that a staff member is hired and assign that date to each subsequent year. However, a staff member can be hired, work for several years, leave and then return. When the staff member leaves and returns I need to caputure the "new" hire year and assign that value to any year moving forward.
Current State: I have created a column where I am able to identify the initial hire date for an employee and assign it to every year for that employee. (ex. Employee hired in 2019, employee's hire year will show 2018 for every year 2017-2022) I can also assign the correct year for the year an employee was rehired. However I have been unable to propogate that date to subsequent years. See the example above. For this particular employee they were hired in 2017 (Hire Year = 2016). They left in 2019. However they were rehired in 2021 (Hire Year =2020). The Hire Year shows correctly in 2021 but incorrectly in 2022 where it still shows 2017.
Ask: I need to create a column that shows the most recent hire year taking into account if an employee was hired and then rehired later on. The hire year needs to appear for every year regardless if the employee were employeed during that year. The hire source needs to be "outside hire" in order for the hire year to change. The correct outcome for the previously referenced example should look like this.
Happy to share part of the dataset but I cannot figure out how to attach it here.
Hi, @pilot_butte ;
You could create a column by dax.
Column =
VAR _OUT= CALCULATE(MIN('Table'[School Year]),
FILTER(ALL('Table'),[StaffUniqueId]=EARLIER('Table'[StaffUniqueId])
&&[Hired From]="Outside Hire"
&&[Hire Year]>=EARLIER('Table'[Hire Year])))
RETURN IF([School Year]>=_OUT&&_OUT<>BLANK(),_OUT-1,CALCULATE(MIN('Table'[Hire Year]),ALLEXCEPT('Table','Table'[StaffUniqueId])))The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @pilot_butte ;
Try it.
Column 2 =
VAR _OUT= CALCULATE(MIN('Table'[School Year]),
FILTER('Table',[StaffUniqueId]=EARLIER('Table'[StaffUniqueId])
&&[Hired From]="Outside Hire"
))
RETURN
IF([School Year]<_OUT||_OUT=BLANK(),[School Year]-1,
CALCULATE(MAX('Table'[School Year]),FILTER('Table',[StaffUniqueId]=EARLIER('Table'[StaffUniqueId])&&[School Year]<=EARLIER('Table'[School Year])&&[Hired From]="Outside Hire"))-1)The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @pilot_butte ;
You could create a column by dax.
Column =
VAR _OUT= CALCULATE(MIN('Table'[School Year]),
FILTER(ALL('Table'),[StaffUniqueId]=EARLIER('Table'[StaffUniqueId])
&&[Hired From]="Outside Hire"
&&[Hire Year]>=EARLIER('Table'[Hire Year])))
RETURN IF([School Year]>=_OUT&&_OUT<>BLANK(),_OUT-1,CALCULATE(MIN('Table'[Hire Year]),ALLEXCEPT('Table','Table'[StaffUniqueId])))The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-yalanwu-msft This solution worked until I found an edge case where an employee left and was rehired multiple times. Please see screenshot below. Therefore the approach of using the minimum no longer yielded accurate results. Any ideas of how to adjust the formula?
This solution worked until I came across a situation where an employee left and returned more than once. This means that taking the minimum value no longer provides accurate hire year. Please see below for example. Please let me know how to modify to account for this situation.
Helpful resources
FabCon Global Hackathon
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Power BI Monthly Update - October 2025
Check out the October 2025 Power BI update to learn about new features.
FabCon Atlanta 2026
Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
