DAX获取第N个最后一个非空值
对于任何给定日期，我想获得最近 3 天非空白销售额的平均销售额。因此，我不仅需要检索最后的非空白销售额（这可能很容易），而且还需要获得倒数第二和倒数第三的销售额。一般来说，我需要 N'th last sales。
样本数据：
+------------+--------+--------+--------+--------+------------------+
| 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 |
+------------+--------+--------+--------+--------+------------------+
N'th 1 是最后一个“非空白”销售。N'th 2 是“最后一个”。预期结果是 N1、N2、N3 的平均值。
链接到示例数据文件，其中包含接受的答案建议的解决方案：
DAX 滚动平均 NonBlanks.pbix
回答
这是我的看法（这是一个措施）：
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])
编辑：
简单解释一下：
- 度量是针对所选日期计算的。如果不存在日期上下文，则假定为最新日期。
- 然后我过滤掉表格，只包含不迟于 curDate 的非空白销售行
- 然后我对日期进行排名，以便最近的 3 个日期始终排在第 1、2 和 3 位。
- 然后我过滤掉所有排名高于 3 的日期
- 最后，我计算剩余 3 个数据点的平均值。
编辑2：
我稍微简化了测量 - lastSalesDate 不是必需的。此外，根据评论中的要求，我保留了第一次尝试，这是使用 TOPN 而不是 ADDCOLUMNS/RANKX/FILTER 组合的修改版本：
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])
编辑3：
一个更通用的度量版本，它只是从Date列中删除过滤器，这实际上是我们所需要的。无需屠宰桌子上的所有其他过滤器：
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])