Forum Discussion
Need help with finding previous row value with categories
Hi, I would like some help with finding the previous row's value. My real dataset has thousands of rows, and several columns that give qualitative data. Here is an example of the data I am working with:
| Name | Date | Seats_remaining |
| Jill | 10/11/2021 | 100 |
| Jill | 10/4/2021 | 400 |
| Jill | 9/27/2021 | 580 |
| Jill | 9/20/2021 | 600 |
| Jill | 9/13/2021 | 600 |
| Jack | 10/11/2021 | 57 |
| Jack | 10/4/2021 | 63 |
| Jack | 9/27/2021 | 68 |
| Jack | 9/20/2021 | 64 |
| Jack | 9/13/2021 | 70 |
I would like to calculate the difference in Seats_remaining between the current and previous week, for each person. The oldest week would ideally have a value of null. This is what I ideally would like my data to look like:
| Name | Date | Seats_remaining | previous row value | difference_between_current_and_previous |
| Jill | 10/11/2021 | 100 | 400 | 300 |
| Jill | 10/4/2021 | 400 | 580 | 180 |
| Jill | 9/27/2021 | 580 | 600 | 20 |
| Jill | 9/20/2021 | 600 | 600 | 0 |
| Jill | 9/13/2021 | 600 | null | null |
| Jack | 10/11/2021 | 57 | 63 | 6 |
| Jack | 10/4/2021 | 63 | 68 | 5 |
| Jack | 9/27/2021 | 68 | 63 | -5 |
| Jack | 9/20/2021 | 64 | 70 | 6 |
| Jack | 9/13/2021 | 70 | null | null |
I've been working on this for longer than I would like to admit, and I have watched a bunch of videos, and looked at a bunch of community posts. I would like help finding the previous row's value, once I have that I can easily find the difference_between_current_and_previous, but if there is an easier way to go right to the difference_between_current_and_previous I am open to that. Thanks!
You can achieve this with either a measure or a calculated column.
Measure:
Difference Between Current and Previous (Measure) = VAR vDate = MAX ( Table1[Date] ) VAR vSeats = MAX ( Table1[Seats_remaining] ) VAR vFirstDate = CALCULATE ( MIN ( Table1[Date] ), ALLEXCEPT ( Table1, Table1[Name] ) ) VAR vPreviousDate = CALCULATE ( MAX ( Table1[Date] ), Table1[Date] < vDate, ALLEXCEPT ( Table1, Table1[Name] ) ) VAR vPreviousSeats = CALCULATE ( MAX ( Table1[Seats_remaining] ), ALLEXCEPT ( Table1, Table1[Name] ), Table1[Date] = vPreviousDate ) VAR vResult = IF ( vDate <> vFirstDate, vPreviousSeats - vSeats ) RETURN vResultCalculated column:
Difference Between Current and Previous (Column) = VAR vDate = Table1[Date] VAR vSeats = Table1[Seats_remaining] VAR vFirstDate = CALCULATE ( MIN ( Table1[Date] ), ALLEXCEPT ( Table1, Table1[Name] ) ) VAR vPreviousDate = CALCULATE ( MAX ( Table1[Date] ), Table1[Date] < vDate, ALLEXCEPT ( Table1, Table1[Name] ) ) VAR vPreviousSeats = CALCULATE ( MAX ( Table1[Seats_remaining] ), ALLEXCEPT ( Table1, Table1[Name] ), Table1[Date] = vPreviousDate ) VAR vResult = IF ( vDate <> vFirstDate, vPreviousSeats - vSeats ) RETURN vResultResult:
1 Reply
- DataInsights
Super User
You can achieve this with either a measure or a calculated column.
Measure:
Difference Between Current and Previous (Measure) = VAR vDate = MAX ( Table1[Date] ) VAR vSeats = MAX ( Table1[Seats_remaining] ) VAR vFirstDate = CALCULATE ( MIN ( Table1[Date] ), ALLEXCEPT ( Table1, Table1[Name] ) ) VAR vPreviousDate = CALCULATE ( MAX ( Table1[Date] ), Table1[Date] < vDate, ALLEXCEPT ( Table1, Table1[Name] ) ) VAR vPreviousSeats = CALCULATE ( MAX ( Table1[Seats_remaining] ), ALLEXCEPT ( Table1, Table1[Name] ), Table1[Date] = vPreviousDate ) VAR vResult = IF ( vDate <> vFirstDate, vPreviousSeats - vSeats ) RETURN vResultCalculated column:
Difference Between Current and Previous (Column) = VAR vDate = Table1[Date] VAR vSeats = Table1[Seats_remaining] VAR vFirstDate = CALCULATE ( MIN ( Table1[Date] ), ALLEXCEPT ( Table1, Table1[Name] ) ) VAR vPreviousDate = CALCULATE ( MAX ( Table1[Date] ), Table1[Date] < vDate, ALLEXCEPT ( Table1, Table1[Name] ) ) VAR vPreviousSeats = CALCULATE ( MAX ( Table1[Seats_remaining] ), ALLEXCEPT ( Table1, Table1[Name] ), Table1[Date] = vPreviousDate ) VAR vResult = IF ( vDate <> vFirstDate, vPreviousSeats - vSeats ) RETURN vResultResult: