Forum Discussion

led10's avatar
led10
New Member
4 years ago
Solved

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 ...
Need Help
  • DataInsights's avatar
    DataInsights
    4 years ago

    led10,

     

    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
    vResult

     

    Calculated 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
    vResult

     

    Result:

     

     

DataInsights's avatar
DataInsights
Icon for Super User rankSuper User
4 years ago

led10,

 

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
    vResult

 

Calculated 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
    vResult

 

Result: