Row context total of previous rows

[VV24]

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 from a (consecutive) date range. And i would like to count the total amount until that moment for the row. The desired outcome is made in 'New column'

Key	Date	Amount	New column
A	1-1-2019	1	1
A	2-1-2019	1	2
A	3-1-2019	1	3
A	4-1-2019	1	4
A	5-1-2019	1	5
A	6-1-2019	1	6
A	7-1-2019	1	7
A	8-1-2019	1	8
B	15-1-2019	1	1
B	16-1-2019	1	2
B	17-1-2019	1	3
B	18-1-2019	1	4

[VV24 · Accepted solution]

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
    )

[Ashish_Mathur · Accepted solution]

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.

[VV24]

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

[VV24 · Accepted solution]

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
    )

[v-danhe-msft]

Hi @VV24 ,

It's pleasant that your problem has been solved, could you please mark one reply as Answered to close this topic?

Regards,

Daniel He