powerbi - 使用if和最大值DAX的运行总计

Question

我想创建一个有关请假的表，并且需要一个累计的总数。

年假列2 DAX是

Annual Leave Column2 = 
CALCULATE (
    SUM (Sheet1[Debit/Credit]),
    ALL ( Sheet1 ),
    FILTER(Sheet1, SUM(Sheet1[Debit/Credit])>20), Sheet1[Date] <= EARLIER ( Sheet1[Date] )
) 

对于第3列是

column 3 = IF( Sheet1[Annual Leave Column2]>20, 20, Sheet1[Annual Leave Column2] )

但是，如果结果已经是20，并且在下一个日期中有-1，则结果仍将计为20并停留在20。
我需要的结果是:

我只是认为，如果该值已经为20，我们可以停止计算吗？如果满足-1，则继续计算。还是有另一种方法呢？
好像我使用IF只是将可视化效果设置为20而未将数据设置为20一样，这就是为什么它被固定在20中的原因，因为总和超过20。

Answer 1

最好的选择是让源系统不允许超过年度上限的年度积分，或者直接为您提供上限的年假金额(而不是尝试在Power BI中进行计算)。

即使源系统未存储有上限的年假金额，使用SQL在查询中进行计算也可能比使用Power BI更容易。 (为此，我建议一个单独的问题。)

为什么我要说这个？

在Excel中，将根据上一行的值逐行计算运行(或累计)总数。这样可以轻松地“覆盖”正在运行的总数并将该覆盖应用于后继的每一行。例如。您可以将运行总计的上限设置为20，而下面的单元格的运行就好像运行总计是20。该上限下方的单元格不知道运行总计实际上不是20。

在DAX中，运行总计是在每一行上独立计算的(意味着每一行查看当前行日期之前的所有行，并计算实际的运行总计)。这样就无法覆盖总运行量(例如，将其上限设置为20)，并将调整后的总运行量送入下一行。下一行始终知道实际的总计是多少。

无法告诉DAX查看先前计算的运行总计并将其添加到DAX，因为列无法引用自身(如user5226582所述，它是循环依赖项)。 DAX与Excel不同，DAX按列而不是按单元格运行计算，因此它不能将列的输出用作同一列的输入。

肮脏和不完整的解决方法

肮脏的解决方法将取决于有多少次年度信用可以被忽略。每次忽略全部或部分年度信用时，都会调整所有后续单元的运行总计。

例如，在17年5月1日，真实的运行总数为20.5，但是您扔掉了0.5。这意味着以后的所有行均基于17年5月1日的总计20，而不是20.5。

您当然可以创建一个计算列，以标识运行总额首次被封顶(2017年5月1日)。然后，您将计算使用前计算的运行总计(在17年5月1日之前)的“调整后的运行总计”，但是在17年5月1日之后将忽略先前的运行总计，而是对5月1日至1日的[借方/贷方]列求和17加上20。(加上20，因为我们知道运行总计在17年5月1日为20，因此不会在[借方/贷方]列的总和中反射(reflect)出来。)

Running Total Is Capped = IF([Annual Leave Column2] > 20, 1, 0)

Running Count of Capped =
CALCULATE (
    SUM ( Sheet1[Running Total Is Capped] ),
    ALL ( Sheet1 ),
    FILTER ( Sheet1, Sheet1[Date] <= EARLIER ( Sheet1[Date] ) )
)

Adjusted Running Total =
IF (
    [Running Count of Capped] = 0,
    [Annual Leave Column2],
    20
        + CALCULATE (
            SUM ( Sheet1[Debit/Credit] ),
            ALL ( Sheet1 ),
            FILTER (
                Sheet1,
                Sheet1[Date] <= EARLIER ( Sheet1[Date] )
                    && Sheet1[Running Count of Capped] > 1
            )
        )
)

但是，此解决方案并没有得到解决，因为它仅在第一次按下上限时才起作用。 每次达到上限时，您都需要以相同的方式调整运行总计，并使用一组新的计算列来调整调整后的运行总计。如果您可以达到上限20次或50次，那么您还需要上述计算列的集合也重复20次或50次。

您不能同时调整所有行的上限，因为第一次调整会影响下一次调整的发生时间。在您的示例数据中，17年8月5日的真实总运行时间为21，这意味着我们希望将其减少到20。但是，由于我们之前已经超过上限3次，因此我们已经节省了3.5天因此，调整后的运行总计为17.5，因此不需要设置上限。

除了您需要的大量计算列之外，该模型也无法很好地容纳增加的数据量。 EARLIER函数是迭代的，这意味着它对每一行运行一次计算。行越多，花费的时间越长。一遍又一遍地使用EARLIER函数，因为这种快速而肮脏的解决方法确实会导致性能下降。我强烈建议您找到其他解决方案，最好是在数据到达Power BI之前。

旁注:如果要使用EARLIER函数，建议您为每一行建立索引，以确保它们具有唯一的编号，而不是依赖日期字段作为索引。如果您在同一日期有多个贷方/借方，则使用日期字段作为索引可能会导致意外结果。