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