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.
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?