Sum for the last x days that has data

[Anonymous]

Amount last 5 days =
CALCULATE (
    SUM ( Table1[Amount] ),
    DATEADD ( 'Date'[Calendar Date], -5, DAY)
)

 I know the above formula will calculate the sum for the last 5 days (today - 5 days).  However I am struggling to find a formula that will sum the amount for the last 5 days that has data. See table below as well.

Date		Amount
3-2-2021		10
3-4-2021		11
3-5-2021		13
3-7-2021		15
3-9-2021		19

[amitchandak · Accepted solution]

@Anonymous ,

Create a rank on date/column

date rank = rankx(Table, [Date],,asc,dense)

then try a measure

measure =
var _max = maxx(allselected(Table), Table[date rank])
return
CALCULATE(sum(Table1[Amount]),Table(all(Table[Date rank]), Table[Date rank]>=_max -5))

Try with help from date table

Rolling 5 day = CALCULATE(sum(Table1[Amount]),DATESINPERIOD('Date'[Date],max('Date'[Date]),-5,Day))

[Anonymous]

@amitchandak this worked for the most part but in my dataset there are some companies that have amounts on all days but other companies do not have amounts every day so the daterank function is assigning a rank to every day in the dataset. Therefore it doesn't work great if a company didn't have an amount every day.  The visuals below should expalin it better. The first visual is showing where the formula works but the second visual shows that the third date has a date rank of 290, therefore the 296-5 won't pick up the 3rd, 4th or 5th dates because its outside of the date rank return.