How to sum the last 5 rows separated by categories in power bi using DAX syntax

[Filipe_Pacheco]

Hi everyone, I'm trying to sum the last 5 rows in a table for each row and divided by category, such as the example below that I did in Excel. The expected result is the column D 'RUNNING_SUM'.

If I would write this in SQL, I would use SUM OVER PARTITION. 

Could you anwser that for me?

.Example in Excel how to do it

[FreemanZ]

hi @Filipe_Pacheco 

could you provide the expected result directly?

[Filipe_Pacheco]

Hi @FreemanZ , I edited the post, but the expected result is the Column D 'RUNNING_SUM'.

[FreemanZ · Accepted solution]

hi @Filipe_Pacheco 

try like:

Coulmn = 
VAR _fam = [Fam]
VAR _year = [YEAR]
RETURN
SUMX(
    FILTER(
        TableName,
        TableName[Fam] = _fam
           &&TableName[Year] >=_year-4
           &&TableName[Year]<=_year
    ),
    TableName[Value]
)

[Filipe_Pacheco]

That one was hard, congratulations, overwhelming 👏