Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
say there is a Values column
5
5
5
-2
-1
5
the Running Sum measure would be:
5
10
15
13
12
17
if one needed a maximum of 12 and applied a simple IF/Switch result would be
5
10
12
12
12
12
but for the running sum to be based on the prior value of its own field/column - intellisense will not allow that measure to get written because of circular reference. The result sought is:
5 5
5 10
5 12
-2 10
-1 9
5 12
perhaps there is another function that will work - or approach
would welcome advice on this one - has me stumped...... I should add one can assume that adding an Index column is possible.
@Anonymous
Solved! Go to Solution.
It should be something like this:
let Source = Excel.Workbook(File.Contents("D:\Projects\Internal CRM\Leave Data.xlsx"), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Type", type text}, {"Debit/Credit", type number}}), RunningSum = List.Skip(List.Accumulate(#"Changed Type"[#"Debit/Credit"],{0},(sum,value) => sum & {List.Min({12,List.Last(sum)+value})})), TableWithRunningSum = Table.FromColumns(Table.ToColumns(#"Changed Type")&{RunningSum},Value.Type(Table.AddColumn(#"Changed Type","Running Sum", each 0, type number))) in TableWithRunningSum
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |