Forum Discussion
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. 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.
8 Replies
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?
pilot_butte , Can you please share raw data , based on the data we can provide better colution
maxx(filter(Table, Table[emp_id] = earlier(Table[Emp_id]) && year([Hire_date]) <=earlier([school_year]) ) , [Hire_date])
Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8
refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8amitchandak stupid question. how do I share data within the forum?
pilot_butte , If the sample is on excel copy - Paste. Or have a file on one drive or dropbox and share a link.
StaffUniqueId School Year PositionCategory District Retained School Retained Hired From Hire_Source Left To Hire Year 122181 2017 Teacher 1 1 2016 122181 2018 Teacher 1 1 2016 122181 2019 Teacher 1 1 2016 122181 2020 Teacher 1 1 2016 122181 2021 Teacher 1 1 2016 122181 2022 Teacher 2016 1352113 2017 School Other 1 1 2016 1352113 2018 School Other 1 1 2016 1352113 2019 School Other 1 1 2016 1352113 2020 School Other 1 1 2016 1352113 2021 School Other 1 1 2016 1352113 2022 School Other 2016 158445 2017 Special Education 1 0 School 2016 158445 2018 Special Education 1 1 Other School 2016 158445 2019 Special Education 0 0 Central_Districts 2016 158445 2020 0 0 2016 158445 2021 Special Education 1 1 Outside Hire Outside Hire 2020 158445 2022 Special Education 2016 1699202 2017 0 0 2017 1699202 2018 Teacher 1 1 Outside Hire Outside Hire 2017 1699202 2019 Teacher 1 1 2017 1699202 2020 Teacher 1 1 2017 1699202 2021 Teacher 1 1 2017 1699202 2022 Teacher 2017 1714678 2017 School Other 1 1 2016 1714678 2018 School Other 1 1 2016 1714678 2019 School Other 1 1 2016 1714678 2020 School Other 1 1 2016 1714678 2021 School Other 1 1 2016 1714678 2022 School Other 2016 1765221 2017 School Administration 1 1 2018 1765221 2018 School Administration 1 0 School 2018 1765221 2019 School Administration 1 1 Other School Elementary School 2018 1765221 2020 School Administration 1 1 2018 1765221 2021 School Administration 1 1 2018 1765221 2022 School Administration 2018 1850245 2017 Staff Support 0 0 Central_Districts 2016 1850245 2018 0 0 2016 1850245 2019 0 0 2016 1850245 2020 0 0 2016 1850245 2021 0 0 2016 1927760 2017 0 0 2017 1927760 2018 Teacher 1 1 Outside Hire Outside Hire 2017 1927760 2019 Teacher 1 1 2017 1927760 2020 Teacher 0 0 Central_Districts 2017 1927760 2021 0 0 2017 1993224 2017 0 0 2017 1993224 2018 Staff Support 0 0 Outside Hire Outside Hire District_Position 2017 1993224 2019 School Other 1 1 Change Position Paraprofessional (Educational Assistant) 2017 1993224 2020 School Other 0 0 District_Position 2017 1993224 2021 Staff Support 1 1 Change Position Other Non-Licensed Staff 2017 1993224 2022 Staff Support 2017 2043319 2017 0 0 2018 2043319 2018 0 0 2018 2043319 2019 Staff Support 1 1 Outside Hire Outside Hire 2018 2043319 2020 Staff Support 0 0 Central_Districts 2018 2043319 2021 0 0 2018 2043319 2022 Staff Support Outside Hire Outside Hire 2021 2067137 2017 0 0 2018 2067137 2018 0 0 2018 2067137 2019 Student Services 1 1 Outside Hire Outside Hire 2018 2067137 2020 Student Services 0 0 Central_Districts 2018 2067137 2021 0 0 2018 2202220 2017 0 0 2019 2202220 2018 0 0 2019 2202220 2019 0 0 2019 2202220 2020 Student Services 0 0 Outside Hire Outside Hire Central_Districts 2019 2202220 2021 0 0 2019 2381699 2017 0 0 2021 2381699 2018 0 0 2021 2381699 2019 0 0 2021 2381699 2020 0 0 2021 2381699 2021 0 0 2021 2381699 2022 School Other Outside Hire Outside Hire 2021 2438577 2017 0 0 2021 2438577 2018 0 0 2021 2438577 2019 0 0 2021 2438577 2020 0 0 2021 2438577 2021 0 0 2021 2438577 2022 Student Services Outside Hire Outside Hire 2021 266736 2017 School Administration 1 1 2016 266736 2018 School Administration 0 0 Central_Districts 2016 266736 2019 0 0 2016 266736 2020 0 0 2016 266736 2021 0 0 2016 963682 2017 Special Education 1 1 2016 963682 2018 Special Education 1 1 2016 963682 2019 Special Education 1 1 2016 963682 2020 Special Education 1 1 2016 963682 2021 Special Education 1 1 2016 963682 2022 Special Education 2016
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.
