Forum Discussion
Power Query Running Totals
- 4 years ago
let
Source = Fact,
#"Filtered Rows" = Table.SelectRows(Source, each ([ProjectKey] = 430) and ([FiscalYear] = 2021)),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"DateKey", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"ProjectKey", "FiscalYear"}, {"Count", each _,}),
// Function to calculate running totals
RunFunction = (RunTable as table) as table=>
let
#"Added Index" = Table.AddIndexColumn(RunTable, "Index", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "n", each {List.Sum(List.Range(#"Added Index" [Revenue],0,[Index])),List.Sum(List.Range(#"Added Index" [CostAmount],0,[Index]))}),Custom1 = Table.SplitColumn(#"Added Custom","n",each _,{"CummulativeRevenue","CummulativeCost"})
in
Custom1,
// call the function
RunTotals= Table.TransformColumns(#"Grouped Rows", {"Count", each RunFunction(_)}),
#"Expanded Count" = Table.ExpandTableColumn(RunTotals, "Count", {"DateKey", "MonthName", "BudgetYTDRevenue", "BudgetYTDCost", "CummulativeRevenue","CummulativeCost"}, {"DateKey", "MonthName", "BudgetYTDRevenue", "BudgetYTDCost", "CummulativeRevenue","CummulativeCost"})in
#"Expanded Count"
let
Source = Fact,
#"Filtered Rows" = Table.SelectRows(Source, each ([ProjectKey] = 430) and ([FiscalYear] = 2021)),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"DateKey", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"ProjectKey", "FiscalYear"}, {"Count", each _,}),
// Function to calculate running totals
RunFunction = (RunTable as table) as table=>
let
#"Added Index" = Table.AddIndexColumn(RunTable, "Index", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "n", each {List.Sum(List.Range(#"Added Index" [Revenue],0,[Index])),List.Sum(List.Range(#"Added Index" [CostAmount],0,[Index]))}),
Custom1 = Table.SplitColumn(#"Added Custom","n",each _,{"CummulativeRevenue","CummulativeCost"})
in
Custom1,
// call the function
RunTotals= Table.TransformColumns(#"Grouped Rows", {"Count", each RunFunction(_)}),
#"Expanded Count" = Table.ExpandTableColumn(RunTotals, "Count", {"DateKey", "MonthName", "BudgetYTDRevenue", "BudgetYTDCost", "CummulativeRevenue","CummulativeCost"}, {"DateKey", "MonthName", "BudgetYTDRevenue", "BudgetYTDCost", "CummulativeRevenue","CummulativeCost"})
in
#"Expanded Count"