DAX获取倒数第N个非空值

Question

对于给定的日期，我想要获取最近三天内非空销售额的平均值。因此，我不仅需要检索最后一个非空销售额（可能很容易），还需要获取倒数第二个和倒数第三个销售额。一般来说，我需要获取第N个最近的销售额。

样本数据：

+------------+--------+--------+--------+--------+------------------+
|    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 |
+------------+--------+--------+--------+--------+------------------+

第N1个是最后一个“非空”销售。第N2个是“倒数第二个”。预期结果是N1、N2和N3的平均值。

样本数据文件链接及已被接受答案建议的解决方案：
DAX Rolling Average NonBlanks.pbix

- Przemyslaw Remin

Answer 1

以下是我的观点（它是一个度量）：

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])

- W.B.

---

只是补充一下 - 采用这种方法，很容易计算任意数量的非空销售的平均值，而不需要引入其他措施。在倒数第二步时，当您过滤排名等于或低于3的表格时，您只需更改一个值（或以切片器为基础），即可完成此操作。 - W.B.

看起来非常优雅和有前途。我正在处理它。nonBlankTab和rankedTab是表格，对吗？ - Przemyslaw Remin

第二次尝试非常出色。如果您能在回答中提及您的第一次尝试，我在此留下它：VAR rankedTab = FILTER ( ADDCOLUMNS ( nonBlankTab, "Rank", RANKX ( nonBlankTab, [Date] ) ), [Rank] <= 3 )，因为它具有灵活性。如果有人只想要第N个元素，他可以通过将<=变为=来获取它。 - Przemyslaw Remin

在CALCULATETABLE行中使用REMOVEFILTERS(Data[Date])的原因是什么？我想将您的解决方案（使用Sales表的日期字段）修改为具有日历表的数据模型。您能否请查看我在答案中提供的示例文件？我无法理解REMOVEFILTERS的原因。 - Przemyslaw Remin

原因是您需要从日期列中删除日期上下文筛选器，因为您要显示任何日期的数据。如果不删除筛选器，则计算仅针对当前行的日期进行。我已经查看了您的文件和这一段代码：'Calendar'[Date] <= _maxDate，它完全相同，但引入了另一个评估，这是不必要的。请参阅 CALCULATE 文档的注释部分：如果列（或表）已经在筛选上下文中，则现有的筛选器将被新筛选器覆盖以评估 CALCULATE 表达式。 - W.B.

Answer 2

首先，创建以下3个度量-

n1 = 
VAR current_date = MIN(your_table_name[Date])
VAR first_max_date_with_no_blank = 
CALCULATE(
    MAX(your_table_name[Date]),
    FILTER(ALL(your_table_name),  your_table_name[Date] <= current_date && your_table_name[Amount] <> BLANK())
)

RETURN
CALCULATE(
    SUM(your_table_name[Amount]),
    FILTER(
        ALL(your_table_name),
        your_table_name[Date] = first_max_date_with_no_blank
    )
)

n2 = 
VAR current_date = MIN(your_table_name[Date])

VAR first_max_date_with_no_blank = 
CALCULATE(
    MAX(your_table_name[Date]),
    FILTER(ALL(your_table_name),  your_table_name[Date] <= current_date && your_table_name[Amount] <> BLANK())
)

VAR second_max_date_with_no_blank = 
CALCULATE(
    MAX(your_table_name[Date]),
    FILTER(ALL(your_table_name),  your_table_name[Date] < first_max_date_with_no_blank && your_table_name[Amount] <> BLANK())
)

RETURN
CALCULATE(
    SUM(your_table_name[Amount]),
    FILTER(
        ALL(your_table_name),
        your_table_name[Date] = second_max_date_with_no_blank
    )
)

n3 = 
VAR current_date = MIN(your_table_name[Date])

VAR first_max_date_with_no_blank = 
CALCULATE(
    MAX(your_table_name[Date]),
    FILTER(ALL(your_table_name),  your_table_name[Date] <= current_date && your_table_name[Amount] <> BLANK())
)

VAR second_max_date_with_no_blank = 
CALCULATE(
    MAX(your_table_name[Date]),
    FILTER(ALL(your_table_name),  your_table_name[Date] < first_max_date_with_no_blank && your_table_name[Amount] <> BLANK())
)

VAR third_max_date_with_no_blank = 
CALCULATE(
    MAX(your_table_name[Date]),
    FILTER(ALL(your_table_name),  your_table_name[Date] < second_max_date_with_no_blank && your_table_name[Amount] <> BLANK())
)

RETURN
CALCULATE(
    SUM(your_table_name[Amount]),
    FILTER(
        ALL(your_table_name),
        your_table_name[Date] = third_max_date_with_no_blank
    )
)

现在创建这个最终度量-

average = 

VAR sum_sales = [n1] + [n2] + [n3]
VAR devide_by = IF([n1] = BLANK(),0,1) + IF([n2] = BLANK(),0,1) + IF([n3] = BLANK(),0,1)

RETURN DIVIDE(sum_sales,devide_by)

这是最终输出 -

- mkRabbani

---

我很感激你在这个答案中所付出的努力。它是易读的教育材料。虽然我希望解决方案更加简洁。 - Przemyslaw Remin