Forum Discussion
Row context total of previous rows
Dear all, I would like to make a calculated column which calculates on the row context, per key the total untill that moment. See the example below, I have three columns, and a key is active fro...
Solved it myself ;), and want to share it, if in any case there is an easier way to do this, im still interested.
My calculated column formula is as follows:
'New column' =
CALCULATE ( SUM ( Table1[Amount] ); ALLEXCEPT ( Table1; Table1[Key] ) )
- DATEDIFF (
Table1[Date];
CALCULATE ( MAX ( Table1[Date] ); ALLEXCEPT ( Table1; Table1[Key] ) );
DAY
)Hi,
This M code (Query Editor) will also work
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key", type text}, {"Date", type datetime}, {"Amount", Int64.Type}}), Partition = Table.Group(#"Changed Type", {"Key"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}), #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Date", "Amount", "Index"}, {"Date", "Amount", "Index"}) in #"Expanded Partition"Hope this helps.
Ashish_Mathur
Super User
Super UserHi,
This M code (Query Editor) will also work
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Key", type text}, {"Date", type datetime}, {"Amount", Int64.Type}}),
Partition = Table.Group(#"Changed Type", {"Key"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
#"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Date", "Amount", "Index"}, {"Date", "Amount", "Index"})
in
#"Expanded Partition"
Hope this helps.
Hi Ashish,
thanks for the reply, had to solve it in Tabular, but its cool to know how to solve such an issue in PQ!
best,
Victor