Forum Discussion
Solved
Calculation referencing previous row, utilizing the value within the same calculated column
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. W...
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 ;
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.
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.