Dynamic Row Numbers

[tlenzmeier]

Hello,

I've been scouring the forum, but haven't found a solution that works for me. I am trying to calculate the net change of a value from the current row to the previous row. Normally, I would consider creating a prior month column, but in my data I can have several rows within the same month.

Sample Data

Project Number        ForecastDate         Official         Amount        RowOverRow Change

1234                        2017-12-15              1                $500               $    0

1234                        2017-12-21              1                $600               $100

1234                        2018-01-05              0                $650               $  50

1234                        2018-02-10              1              $1,000              $350

The total change is $500. I've tried using the "EARLIER" function and the LOOKUPVALUE function, but I cannot get that to work. I've also gone back to edit queries and added an index column.

Thanks!

[Zubair_Muhammad]

Hi @tlenzmeier

Try this calculated Column

Row Over Row Change =
VAR previousdate =
    CALCULATE (
        MAX ( TableName[ForecastDate] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Project Number] ),
            TableName[ForecastDate] < EARLIER ( TableName[ForecastDate] )
        )
    )
VAR PreviousAmount =
    CALCULATE (
        SUM ( TableName[Amount] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Project Number] ),
            TableName[ForecastDate] = previousdate
        )
    )
RETURN
    IF ( NOT ( ISBLANK ( previousdate ) ), TableName[Amount] - PreviousAmount )

[Zubair_Muhammad · Accepted solution]

@tlenzmeier

[tlenzmeier]

Excellent. Thank you.