Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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
)
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.
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.
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
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 @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