Getting Data From Previous Row Based On Two Criteria

[ntaylo06]

I have have the table 'Data' with the fields 'employee ID', 'rotation number', and 'location' with each employee ID appearing three times with rotation number increasing each time and a location code per row. I am trying to add a 'last location' calculated column that will display the last location by looking at the rotation number and subtracting 1 from it, unless the rotation number is 1 which gets the string "xxxxx" like this:

+---------------+-------------------+------------+-----------------+
|  employee ID  |  rotation number  |  location  |  last location  |
+---------------+-------------------+------------+-----------------+ 
1001               1                  abcde       xxxxx
1001               2                  fghij       abcde
1001               3                  klmno       fghij 
1002               1                  pqrst       xxxxx
1002               2                  uvwx        pqrst     
1002               3                  yzab        uvwx 

Can anyone help with this?

[HotChilli]

Is the last value in 'last location' a mistake? Should it be uvwx?

If so, try adding a column

Column = var _emp = Table1[employee ID]
var _rot = Table1[rotation number]
RETURN
if (Table1[rotation number] <> 1, 
     CALCULATE(MAX(Table1[location]),FILTER(Table1, Table1[employee ID] = _emp && Table1[rotation number] < _rot)),
    "xxxxx")

[ntaylo06]

---

@HotChilli wrote:

Is the last value in 'last location' a mistake? Should it be uvwx?

Yes, that was a mistake. I've fixed it now.

---

@HotChilli wrote:

If so, try adding a column

---

The column kinda works, but it is only pulling the entry for rotation #2 and then displaying it for all rows after the first:

+---------------+-------------------+------------+-----------------+
|  employee ID  |  rotation number  |  location  |  last location  |
+---------------+-------------------+------------+-----------------+ 
1001               1                  abcde       xxxxx
1001               2                  fghij       abcde
1001               3                  klmno       abcde
1002               1                  pqrst       xxxxx
1002               2                  uvwx        pqrst     
1002               3                  yzab        pqrst 

[HotChilli]

Do you want to link your pbix here and i'll have a look at it?

[ntaylo06 · Accepted solution]

@HotChilli Thats alright, I found a workaround solution by adding an index column and then calculating index-1 from it. Thanks for trying, though.

[v-chuncz-msft]

@ntaylo06 

Glad to hear that. You may help accept the solution above. Your contribution is highly appreciated.