Forum Discussion

pilot_butte's avatar
pilot_butte
Frequent Visitor
3 years ago
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...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    3 years ago

    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's avatar
v-yalanwu-msft
Community Support
3 years ago

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.