Calculating last 5 items for each row of the table

0
votes

Power BI Dax question:

I have the following table:

Table

How can i have a running calculation using a measure which sums the 5 previous rows from "Status" column? See the photo below for the idea in excel

Calculation

Any help would be greatly appreciated!

1
powerbidax
You Time column holds all same date. So how order order data to define last 5 rows? You must have some other column like - ID or Index to apply the ordering. – mkRabbani

1 Answers

0
votes

You can add an extra column and do a top5 filter.

SumStatusLast5 = 
VAR curDate = yourTable['TIME']
VAR allRowsBefore = FILTER(yourTable, curDate >= yourTable['TIME'])
RETURN CALCULATE(SUM[Status], TOPN(5, allRowsBefore, yourTable['TIME']))

In the first step I get the curDate of the row In the second step I get all rows where the date is equal or smaller In the last step, I take the top5 of those returned rows and sum them together