DAX get N'th last non-blank value

Question

For any given date, I would like to get an average Sales of the most recent 3 days with non-blank sales. So I need to retrieve not only the last non-blank sales (which might be easy) but I also need to get the second last and third last sales. Generally, I need N'th last sales.

Sample data:

+------------+--------+--------+--------+--------+------------------+
|    Date    | Amount | N'th 1 | N'th 2 | N'th 3 | Expected Results |
+------------+--------+--------+--------+--------+------------------+
| 2021-02-01 |      1 |      1 |        |        |             1.00 |
| 2021-02-02 |      2 |      2 |      1 |        |             1.50 |
| 2021-02-03 |      2 |      2 |      2 |      1 |             1.67 |
| 2021-02-04 |        |      2 |      2 |      1 |             1.67 |
| 2021-02-05 |      3 |      3 |      2 |      2 |             2.33 |
| 2021-02-06 |        |      3 |      2 |      2 |             2.33 |
| 2021-02-07 |        |      3 |      2 |      2 |             2.33 |
| 2021-02-08 |      4 |      4 |      3 |      2 |             3.00 |
| 2021-02-09 |        |      4 |      3 |      2 |             3.00 |
| 2021-02-10 |        |      4 |      3 |      2 |             3.00 |
| 2021-02-11 |        |      4 |      3 |      2 |             3.00 |
+------------+--------+--------+--------+--------+------------------+

The N'th 1 is the last "non-blank" sales. The N'th 2 is the "last but one". The expected result is the average of N1, N2, N3.

Link to sample data file with solutions suggested by accepted answer:
DAX Rolling Average NonBlanks.pbix

Answer 1

Here's my take (it's a measure):

Non-blank average = 
var curDate = SELECTEDVALUE(Data[Date], MAX(Data[Date]))
var nonBlankTab = FILTER(ALL(Data), NOT(ISBLANK(Data[Amount])) && Data[Date] <= curDate)
var rankedTab = FILTER ( ADDCOLUMNS ( nonBlankTab, "Rank", RANKX ( nonBlankTab, [Date] ) ), [Rank] <= 3 )
return AVERAGEX(rankedTab, [Amount])

EDIT:

Just an explanation:

• the measure is calculated for the selected date. If no date context is present, the latest date is assumed.
• Then I filter out the table to contain only rows with non blank sales not later than curDate
• Then I rank the dates so that the latest 3 dates always receive ranks 1, 2 and 3.
• Then I filter out all the dates with rank higher than 3
• Finally, I calculate an average over the remaining 3 data points.

EDIT2:

I simplified the measure a bit - lastSalesDate is not necessary. Also, as per request in the comments, I left the first attempt as it was, and here is the modified version with TOPN instead of ADDCOLUMNS/RANKX/FILTER combo:

Non-blank average = 
var curDate = SELECTEDVALUE(Data[Date], MAX(Data[Date]))
var nonBlankTab = FILTER(ALL(Data), NOT(ISBLANK(Data[Amount])) && Data[Date] <= curDate)
var rankedTab = TOPN(3, nonBlankTab, [Date])
return AVERAGEX(rankedTab, [Amount])

EDIT3:

A more universal version of the measure that just removes filters from Date column, which is actually all we need. No need to butcher all the other filters on the table:

Non-blank average = 
var curDate = SELECTEDVALUE(Data[Date], MAX(Data[Date]))
var nonBlankTab = CALCULATETABLE(FILTER(Data, NOT(ISBLANK(Data[Amount])) && Data[Date] <= curDate), REMOVEFILTERS(Data[Date]))
var rankedTab = TOPN(3, nonBlankTab, [Date])
return AVERAGEX(rankedTab, [Amount])