How can I calculate a sum from most recent 5 rows?

[Bvalen]

Hi Everyone,

This is probably very simple but has been breaking my spirit trying to resolve. 

I have a dataset that looks like this:

And in the end highlighted column, I have calculated the using the following DAX:

Home Team Over 2.5 Count = 
CALCULATE (
    SUM ( Fixtures[Over 2.5] ),
    FILTER (
        Fixtures,
        ISNUMBER ( Fixtures[Total Goals] )
            && Fixtures[Date] < EARLIER ( Fixtures[Date])
            && Fixtures[Home Team] = EARLIER(Fixtures[Home Team]))
    )

As you can see, this calculation is totalling all the rows in the table where the date is before the current row date and the home team is equal to the current row home team. I would like to take this calculation further by having it sum only the 5 most recent rows where the home team is equal to the row home team and the dates are before the current row date.

I've tried to use TOPN function in what makes logical sense to me, but without success. Does anyone have any idea on how this could be done?

TIA

[AlB]

Hi @Bvalen 

The easiest way would be to add an index in PQ that gives you the order per date and home team. With that,  you can just  do the filtering  based on

Fixtures[Index] >= (EARLIER ( Fixtures[Index]) - 5)  &&  Fixtures[Index] < EARLIER ( Fixtures[Index])

It can also be done without the index, looking for the last 5 dates before the filtering, but it gets unnecessarily complicated.

Add some sample data and we can get more specific. 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

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

Cheers 

[amitchandak · Accepted solution]

@Bvalen , Create rank column and trying using

new columns

rank = rankx(filter(Fixtures,Fixtures[Home Team] ,= EARLIER(Fixtures[Home Team]), Fixtures[Date],,asc,dense)

Home Team Over 2.5 Count =
CALCULATE (
SUM ( Fixtures[Over 2.5] ),
FILTER (
Fixtures,
ISNUMBER ( Fixtures[Total Goals] )
&& Fixtures[rank] < EARLIER ( Fixtures[rank]) && && Fixtures[rank] >= EARLIER ( Fixtures[rank]) -5
&& Fixtures[Home Team] = EARLIER(Fixtures[Home Team]))
)