calculate average of previous sequence number (week vs previous week deltas)

[RodrigoZD]

Let's say I want to do a comparison of RODI (return on dollar invested) week over week. Since every year the weeks will start on different dates, I created a column with the week number and year, and created a sequence number everytime it changes, but since I have a lot of data for each week, when I summerize in a table, it looks something like this:

YearAndWeek     Total Rodi     AvgSequenceNumber
    201701          1.23                1
    201702          1.21             9648
    201703          1.22            18230

So basically what I am trying to do is to create a new column that works like a LAG function according to the YearAndWeek or SequenceNumber (should be the same)... 
That would give me an output like this:

YearAndWeek     Total Rodi     AvgSequenceNumber   PreviousWeek
    201701          1.23                1                                            
    201702          1.21             9648              1.23
    201703          1.22            18230              1.21

In order to do that, what I've been trying to do is:

PreviousWeek= 
            CALCULATE(AVERAGE(Table[RODI]),
                FILTER(
                    ALL(Table),
                    Table[SequenceNumber] < (MAX(Table[SequenceNumber]))
                    )
                    )

The problem with this, is that it works for the first row, but then it takes on average ALL the previous sequence numbers, instead of just the previous sequence number. (Please note that I cannot use a SequenceNumber-1 because since I have a lot of rows that are the same, the sequence number is not increasing 1 by 1).

[RodrigoZD · Accepted solution]

Found a solution myself...

PreviousWeek = 
            CALCULATE(AVERAGE(Table[RODI]),
                TOPN(1,FILTER(ALL(Table),
                            Table[SequenceNumber] < MAX(Table[SequenceNumber])),Table[SequenceNumber],DESC))

This way I only get the last previous sequence number.

Hope this may help you!