CALCULATE(
SUM(Leave[Daily Balance]),
FILTER(
ALLEXCEPT(Leave, Leave[Employee Id]),
Leave[Date] <= EARLIER(Leave[Date])
))
概述
要求PowerBI完成这项任务是一个具有挑战性的事情，因此可能很难找到一种整洁的方法。
最大的问题是PowerBI的数据模型不支持运行总数的概念——至少不是我们在Excel中所做的那样。在Excel中，一列可以引用出现在该列“前一行”的值，然后通过某个在不同列中列出的“每日变化”进行调整。
PowerBI只能通过对某些行的所有每日更改求和来模拟这一点。我们获取当前行中的日期值并创建一个过滤表，其中所有日期均小于此当前行的日期，然后从该子集中汇总所有每日更改。这似乎是一个微小的差异，但它是非常重要的：
这意味着没有办法“覆盖”我们的运行总数。正在执行的唯一计算是发生在包含每日更改的列上，包含“运行总数”的列仅是结果——它从不用于任何后续行的计算。
我们必须放弃“重置”的概念，而是想象制作一个包含“调整”值的列。我们的调整将是一个可以包含的值，以便在满足所描述的条件时，每日余额和调整的总和将总和为1。
如果我们查看OP给出的计算的累加值，我们会发现在“工作”日之前的“非工作”日上的运行总数的值给出了我们所需的金额，如果反转，将总和为零，并导致每个后续工作日的运行总数增加1。这是我们期望的行为（有一个稍后描述的问题）。
结果
Most Recent Date Prior to Work =
CALCULATE(
Max(Leave[Date]),
FILTER(
ALLEXCEPT(Leave, Leave[Id]),
Leave[Date] = EARLIER(Leave[Date]) -1 && Leave[Type] <> "Working" && Earlier(Leave[Type]) = "Working"
))
了解行上下文和筛选上下文的区别以及EARLIER操作是如何进行计算的会有所帮助。在这种情况下，您可以将“EARLIER”理解为“此引用指向当前行中的值”，否则引用就指向由“ALLEXCEPT（Leave，Leave [Id]）”返回的整个表。通过这种方式，我们找到了当前行类型为“Working”且前一天的行具有其他类型的地方。
Most Recent Date Prior to Work Complete =
CALCULATE(
Max(Leave[Most Recent Date Prior to Work]),
FILTER(
ALLEXCEPT(Leave, Leave[Id]),
Leave[Date] <= EARLIER(Leave[Date])
))
Daily Balance Adjustment =
CALCULATE(
SUM(Leave[Running Daily Balance]),
FILTER(
ALLEXCEPT(Leave, Leave[Id]),
Leave[Date] = EARLIER(Leave[Most Recent Date Prior to Work Complete])
))
现在每一行都有一个字段解释了去哪里查找每日余额作为我们的调整，我们可以直接从表格中查找。
Adjusted Daily Balance = Leave[Running Daily Balance] - Leave[Daily Balance Adjustment]
我想我有了！
以下是结果，基于我之前发布的解决方案构建：（为展示更多“工作/不工作”行为和用例，数据已进行修改）
结果
详细信息
(1) 删除“调整后的日结余”和“每日余额调整”列。我们稍后只需少一步即可得到相同的结果。
(2) 创建以下列（RDB = “日结余”）...
Grouped RDB =
CALCULATE(
SUM(Leave[Daily Balance]),
FILTER(
ALLEXCEPT(Leave, Leave[Id], Leave[Most Recent Date Prior to Work Complete]),
Leave[Date] <= EARLIER(Leave[Date])
))
创建了“最近工作完成日期”之后，我们已经拥有了需要进行“重置”的部分内容，而这在之前是不可能的。通过对此字段进行筛选，我们有机会从“1”开始重新启动每个切片。
(3) 然而，我们仍然存在同样的问题，即无法查看列中的结果并使用它来决定稍后在该列中要执行的操作。但是，我们可以构建一个新的调整列来保存该信息！而且，我们已经引用了“工作完成日期前最近的日期”——即上一组中的最后一天...该行包含了我们需要的信息！
Grouped RDB Adjustment =
VAR CalculatedAdjustment =
CALCULATE(
SUM(Leave[Grouped RDB]),
FILTER(
ALLEXCEPT(Leave, Leave[Id]),
Leave[Date] IN SELECTCOLUMNS(
FILTER(
Leave,
Leave[Most Recent Date Prior to Work] <> BLANK() &&
Leave[id] = EARLIER(Leave[Id])), "MRDPtW", Leave[Most Recent Date Prior to Work]) &&
Leave[Most Recent Date Prior to Work Complete] < EARLIER(Leave[Most Recent Date Prior to Work Complete]) &&
Leave[Most Recent Date Prior to Work Complete] <> Blank()
))
RETURN if (CalculatedAdjustment > 0, CalculatedAdjustment, 0)
因此，我们查看每个先前组中的最后一天，如果这些调整的总和为正值，则应用该值，如果为负值，则不进行调整。此外，如果我们的人员的前几天是非工作日，则我们根本不希望在我们的调整中有任何初始负面影响，因此它也被过滤掉。
(4) 这最后一步将调整带入最终结果。将两列相加，我们应该最终得到我们的调整后每日运营余额。大功告成！
Adjusted Running Daily Balance = Leave[Grouped RDB] + Leave[Grouped RDB Adjustment]
我们一路上建立了许多额外的列，这通常不是我喜欢做的事情。但是，这个任务很棘手。
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUMzDSMzIwtFTSUQpILSrOz1MwBDLL84uyM/PSlWJ1gGqMsKuBSBrjkzQhwnRTItSYEaHGHJ9DLPBJWhI23dAAjwGGOAIRIokj9OCmxwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [date = _t, name = _t, #"type" = _t]),
SetTypes = Table.TransformColumnTypes(Source,{{"date", type date}, {"name", type text}, {"type", type text}}),
TempColumn1 = Table.AddColumn(SetTypes, "LastOtherType", (row)=>List.Max(Table.SelectRows(SetTypes, each ([name] = row[name] and [type] <> row[type] and [date] <= row[date]))[date], row[date]), type date) //Here for each row we select all rows of other type with earlier date, and take max that date. Thus we know when was previous change from one type to another
//Here for each row we select all rows of other type with earlier date, and take max that date. Thus we know when was previous change from one type to another
,
TempColumn2 = Table.AddColumn(TempColumn1, "Count", (row)=>
(if row[type]="working" then 1 else -1) *
Table.RowCount(
Table.SelectRows(SetTypes, each ([name] = row[name] and [type] = row[type] and [date] <= row[date] and [date] > row[LastOtherType])) /* select all rows between type change (see prev step) and current row */
), /*and count them*/
Int64.Type) // finally multiply -1 if they are not working type
,
FinalColumn = Table.AddColumn(TempColumn2, "FinalFormula", (row)=>
(if row[type] = "working" then row[Count] else /* for working days use Count, for others take prev max Count and add current Count, which is negative for non-working*/
Table.LastN(Table.SelectRows(TempColumn2, each [name] = row[name] and [type] = "working" and [LastOtherType] <= row[LastOtherType]),1)[Count]{0}
+ row[Count])
, Int64.Type),
RemovedTempColumns = Table.RemoveColumns(FinalColumn,{"LastOtherType", "Count"})
in
RemovedTempColumns
Running Total =
VAR Employee = Leave[Employee ID]
VAR Date1 = Leave[Date]
VAR Prev_Blank = CALCULATE(MAX(Leave[Date]),
FILTER(Leave,Leave[Date] < Date1),
FILTER(Leave,Leave[Employee ID]=Employee),
FILTER(Leave,Leave[Type]=BLANK()))
VAR Day_count_Working = CALCULATE(COUNT(Leave[Date]),
FILTER(Leave,Leave[Date] > Prev_Blank),
FILTER(Leave,Leave[Date] <= Date1),
FILTER(Leave,Leave[Employee ID]=Employee),
FILTER(Leave,Leave[Type]="Working"))
VAR Day_count = CALCULATE(COUNT(Leave[Date]),
FILTER(Leave,Leave[Date] >= Prev_Blank),
FILTER(Leave,Leave[Date] <= Date1),
FILTER(Leave,Leave[Employee ID]=Employee))
RETURN (IF(Day_count_Working=BLANK(),Day_count,Day_count-1)-Day_count_Working)*-1 + Day_count_Working
请记住，这可能不是最终产品，因为我只是使用了一个小样本，但这应该可以让您开始。希望这能帮到您。
Running Total =
VAR Employee = Leave[Employee ID]
VAR Date1 = Leave[Date]
VAR Prev_Blank = CALCULATE(MAX(Leave[Date]),
FILTER(Leave,Leave[Date] < Date1),
FILTER(Leave,Leave[Employee ID]=Employee),
FILTER(Leave,Leave[Type]=BLANK()))
VAR Prev_Working = CALCULATE(MAX(Leave[Date]),
FILTER(Leave,Leave[Date] < Date1),
FILTER(Leave,Leave[Employee ID]=Employee),
FILTER(Leave,Leave[Type]="Working"))
VAR Prev_Blank1 = CALCULATE(MAX(Leave[Date]),
FILTER(Leave,Leave[Date] < Prev_Working),
FILTER(Leave,Leave[Employee ID]=Employee),
FILTER(Leave,Leave[Type]=BLANK()))
VAR Prev_type = CALCULATE(MAX(Leave[Type]),
FILTER(Leave,Leave[Date] = Date1-1),
FILTER(Leave,Leave[Employee ID]=Employee))
VAR Prev_Blank2 = IF(Leave[Type]="Working" && (Prev_Blank1=BLANK() || Prev_type=BLANK()),Date1-1,Prev_Blank1)
VAR Day_count_Working = CALCULATE(COUNT(Leave[Date]),
FILTER(Leave,Leave[Date] > Prev_Blank2),
FILTER(Leave,Leave[Date] <= Date1),
FILTER(Leave,Leave[Employee ID]=Employee),
FILTER(Leave,Leave[Type]="Working"))
VAR Day_count = CALCULATE(COUNT(Leave[Date]),
FILTER(Leave,Leave[Date] >= Prev_Blank2),
FILTER(Leave,Leave[Date] <= Date1),
FILTER(Leave,Leave[Employee ID]=Employee))
RETURN (IF(Day_count_Working=BLANK(),Day_count,Day_count-1)-Day_count_Working)*-1 + Day_count_Working
(MyTable as table) =>
let
SelectJustWhatsNeeded = Table.SelectColumns(MyTable,{"Type", "Daily Allowance", "Adjustments"}),
ReplaceNulls = Table.ReplaceValue(SelectJustWhatsNeeded,null,0,Replacer.ReplaceValue,{"Adjustments"}),
#"Merged Columns" = Table.CombineColumns(ReplaceNulls,{"Daily Allowance", "Adjustments"}, List.Sum,"Amount"),
TransformToList = List.Buffer(Table.ToRecords(#"Merged Columns")),
ConditionalRunningTotal = List.Skip(List.Generate(
() => [Type = TransformToList{0}[Type], Result = 0, Counter = 0],
each [Counter] <= List.Count(TransformToList),
each [
Result = 如果 TransformToList{[Counter]}[Type] = "working" 且 [Result] < 0 且 [Type] <> "working"
则 TransformToList{[Counter]}[Amount]
否则 TransformToList{[Counter]}[Amount] + [Result] ,
Type = TransformToList{[Counter]}[Type],
Counter = [Counter] + 1
],
each [Result]
)),
Custom1 = Table.FromColumns( Table.ToColumns(MyTable) & {ConditionalRunningTotal}, Table.ColumnNames(MyTable) & {"Result"} )
in
Custom1