Referring Data from Previous Row in a Query

[Anonymous]

I have a table with two columns, Date and Average, that shows the the average of scores for each date.

Example:

DATE                      AVERAGE

05/26/2020            98.1

06/3/2020              94

I want to add another column that gives the % difference between the scores . How do I do this by referring data from the previous row?

[AlB · Accepted solution]

Hi @Anonymous 

You might want to tweak the way you calculate the percentage change in the RETURN statement. You can convert the column to %

Calc column =
VAR previousDate_ =
    CALCULATE (
        MAX ( Table1[Date] ),
        ALL ( Table1 ),
        Table1[Date] < EARLIER ( Table1[Date] )
    )
VAR previousDateAvg_ =
    CALCULATE (
        MAX ( Table1[Average] ),
        ALL ( Table1 ),
        Table1[Date] = previousDate_
    )
VAR currentDateAvg_ = Table1[Average]
RETURN
    DIVIDE ( currentDateAvg - previousDateAvg_, previousDateAvg_ )

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

You can also create a measure to get difference.

First, create an index column in the query editor to get the value of the previous "average".

Then create a measure like this:

Measure =
VAR current_index =
    MAX ( 'Table (3)'[Index] )
VAR last_average =
    CALCULATE (
        FIRSTNONBLANK ( 'Table (3)'[Average], 1 ),
        FILTER ( ALL ( 'Table (3)' ), 'Table (3)'[Index] = current_index - 1 )
    )
RETURN
    DIVIDE ( last_average, MAX ( 'Table (3)'[Average] ) )

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlB · Accepted solution]

Hi @Anonymous 

You might want to tweak the way you calculate the percentage change in the RETURN statement. You can convert the column to %

Calc column =
VAR previousDate_ =
    CALCULATE (
        MAX ( Table1[Date] ),
        ALL ( Table1 ),
        Table1[Date] < EARLIER ( Table1[Date] )
    )
VAR previousDateAvg_ =
    CALCULATE (
        MAX ( Table1[Average] ),
        ALL ( Table1 ),
        Table1[Date] = previousDate_
    )
VAR currentDateAvg_ = Table1[Average]
RETURN
    DIVIDE ( currentDateAvg - previousDateAvg_, previousDateAvg_ )

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers