running delta of cumulative amount

[rashidanwar]

Hi every one,

I have some financial data in the the below format. Amount column actually cumulative. I need this cumulative column to be converted to the Required Output Column. 
In Jan 2021 I had only 2 income streams and in Feb ther are 3. More Income Streams may be added with the passage of time I need the differentail amount (Revenue for each month) that how much amount is at the Jan 2021 and what is the incerment to each stream each month. 

One more comlication is added, as the we move the the next year. Cumulation occurs from JAN to DEC every year. As we start new year we starts from the revenue for the month of JAN and then accumulate that with each passing month, to DEC. 

Required ouput column is actually what do I need.
Provide the solution in DAX or Query Editor, whatever way is the most efficeint. 

Any help would be much appreciated 🙂

Date                 Account Parent Name      Account Name            Account Code             Amount ($)        Required Output
1/31/2021          Revenue                           Stream 1                      51100                           40,000            40,000
1/31/2021          Revenue                           Stream 2                      51200                           20,000            20,000
2/28/2021          Revenue                           Stream 1                      51100                           95,000            45,000
2/28/2021          Revenue                           Stream 2                      51200                           35,000            15,000
2/28/2021          Revenue                           Stream 3                      51300                           10,000             10,000
3/31/2021          Revenue                           Stream 1                      51100                           120,000           25,000
3/31/2021          Revenue                           Stream 2                      51200                           51,000             16,000
3/31/2021          Revenue                           Stream 3                      51300                           20,000             10,000

1/31/2022          Revenue                           Stream 1                      51100                           50,000              50,000
1/31/2022          Revenue                           Stream 2                      51200                           23,000              23,000
1/31/2022          Revenue                           Stream 3                      51300                           12,000              12,000
2/28/2022          Revenue                           Stream 1                      51100                           100,000             50,000
2/28/2022          Revenue                           Stream 2                      51200                           40,000               17,000
2/28/2022          Revenue                           Stream 3                      51300                           25,000               13,000
3/31/2022          Revenue                           Stream 1                      51100                           145,000              45,000
3/31/2022          Revenue                           Stream 2                      51200                           58,000                18,000
3/31/2022          Revenue                           Stream 3                      51300                           40,000                15,000

Regards
Rashid Anwar

[ronrsnfld · Accepted solution]

Here's another method:

• Add a "year" column
• Group by "year" and "Account Name"
• Use the List.Generate function to create a list of the Increments for each sub group.
• Re-expand and sort things back to original order

let
    Source = Excel.CurrentWorkbook(){[Name="Table28"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Date", type date}, {"Account Parent Name", type text}, {"Account Name", type text}, 
        {"Account Code", Int64.Type}, {"Amount ($)", Currency.Type}}),

//Add year column
//Then group by year and account name
//use List.Generate to generate a column of increments to be added to the original sub-table
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Year", "Account Name"}, {
        {"Increment", (t)=>
            Table.FromColumns(
                Table.ToColumns(t) &
                    {List.Generate(
                        ()=>[incr=t[#"Amount ($)"]{0}, idx=0],
                        each [idx] < Table.RowCount(t),
                        each [incr = t[#"Amount ($)"]{[idx]+1} - t[#"Amount ($)"]{[idx]}, idx=[idx]+1],
                        each [incr])},
                type table[Date=date, Account Parent Name=text, Account Name=text, Account Code=Int64.Type, #"Amount ($)"=Currency.Type, Year=Int64.Type, Increment=Currency.Type])}
                }),

//remove unneeded columns
//Expand the table
//Sort back to original order
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Year", "Account Name"}),
    #"Expanded Increment" = Table.ExpandTableColumn(#"Removed Columns", "Increment", 
        {"Date", "Account Parent Name", "Account Name", "Account Code", "Amount ($)", "Increment"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Increment",{{"Date", Order.Ascending}, {"Account Name", Order.Ascending}}),

//reset the datatypes
       #"Changed Type1" = Table.TransformColumnTypes(#"Sorted Rows",{
        {"Date", type date}, {"Account Parent Name", type text}, {"Account Name", type text}, 
        {"Account Code", Int64.Type}, {"Amount ($)", Currency.Type},{"Increment", Currency.Type}})

in
    #"Changed Type1"

Original

Processed

[rashidanwar]

@serpiva64  and @ronrsnfld thank you so much for all your kind help. 
I uded the query editor and following helped me in getting this done.
I wathced this video 

#"Sorted Rows" = Table.Sort(#"Promoted Headers",{{"Date", Order.Ascending}}),
#"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"Date", type date}, {"Account Parent Name", type text}, {"Account Name", type text}, {"Account Code", Int64.Type}, {"Amount ($)", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Amount ($)", "Amount"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Date] <> null)),
#"Added Year Col" = Table.AddColumn(#"Filtered Rows", "Year", each Date.Year([Date])),
#"Grouped Rows" = Table.Group(#"Added Year Col", {"Year", "Account Code"}, {{"all_rows", each _, type table [Date=nullable date, Account Parent Name=nullable text, Account Name=nullable text, Account Code=nullable number, #"Amount ($)"=nullable number, Year=number]}}),
Custom1 = Table.TransformColumns(
#"Grouped Rows",
{{"all_rows", each Table.AddIndexColumn(_,"Index")}}
),
#"Added Custom" = Table.AddColumn(Custom1, "Custom", each let
AllTable = [all_rows],
PreRowValue =
Table.AddColumn(
AllTable,"IncrementalAmount",
each try AllTable[Amount] {[Index]} - AllTable[Amount] {[Index]-1}
otherwise AllTable[Amount] {[Index]}
)
in
PreRowValue),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Date", "Account Parent Name", "Account Name", "Account Code", "Amount", "IncrementalAmount"}, {"Date", "Account Parent Name", "Account Name", "Account Code", "Amount", "IncrementalAmount"})
in
#"Expanded Custom"

[ronrsnfld · Accepted solution]

Here's another method:

• Add a "year" column
• Group by "year" and "Account Name"
• Use the List.Generate function to create a list of the Increments for each sub group.
• Re-expand and sort things back to original order

let
    Source = Excel.CurrentWorkbook(){[Name="Table28"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Date", type date}, {"Account Parent Name", type text}, {"Account Name", type text}, 
        {"Account Code", Int64.Type}, {"Amount ($)", Currency.Type}}),

//Add year column
//Then group by year and account name
//use List.Generate to generate a column of increments to be added to the original sub-table
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Year", "Account Name"}, {
        {"Increment", (t)=>
            Table.FromColumns(
                Table.ToColumns(t) &
                    {List.Generate(
                        ()=>[incr=t[#"Amount ($)"]{0}, idx=0],
                        each [idx] < Table.RowCount(t),
                        each [incr = t[#"Amount ($)"]{[idx]+1} - t[#"Amount ($)"]{[idx]}, idx=[idx]+1],
                        each [incr])},
                type table[Date=date, Account Parent Name=text, Account Name=text, Account Code=Int64.Type, #"Amount ($)"=Currency.Type, Year=Int64.Type, Increment=Currency.Type])}
                }),

//remove unneeded columns
//Expand the table
//Sort back to original order
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Year", "Account Name"}),
    #"Expanded Increment" = Table.ExpandTableColumn(#"Removed Columns", "Increment", 
        {"Date", "Account Parent Name", "Account Name", "Account Code", "Amount ($)", "Increment"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Increment",{{"Date", Order.Ascending}, {"Account Name", Order.Ascending}}),

//reset the datatypes
       #"Changed Type1" = Table.TransformColumnTypes(#"Sorted Rows",{
        {"Date", type date}, {"Account Parent Name", type text}, {"Account Name", type text}, 
        {"Account Code", Int64.Type}, {"Amount ($)", Currency.Type},{"Increment", Currency.Type}})

in
    #"Changed Type1"

Original

Processed

[serpiva64]

Hi,

to obtain this in power query:

You need to pass trough this steps:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vdWxDoIwEAbgV2mYSehdOcXX0JEwOHTUwajPrwgi5G8ZjlrGKyQf9/fati2oclSxZTLTc/RPf314E39O95s/XwyFV4XI2pWva1va5QtDpejKbR6OeXjVw+Dhn4crbjL35yDQH0niUfbHgYfSeFzM41Y9BHmNpQ/I5d/QBDuIJYlHGZgQNmiXBKRMDCdsnth35Gd/++/EBEFjbbNIewg5FA21zSLtmDGKhtpi8DOm9l4FkuBRnTE1vMp65j4JSTtrcFr3JAfjnzO3OmCq8YjMGJw0gS4ZapKYlMkFN9N013Yv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date Account Parent Name Account Name Account Code Amount ($) Required Output" = _t]),
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Date Account Parent Name Account Name Account Code Amount ($) Required Output", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Date Account Parent Name Account Name Account Co", "Date Account Parent Name Account Name Account .1", "Date Account Parent Name Account Name Account .2", "Date Account Parent Name Account Name Account .3", "Date Account Parent Name Account Name Account .4", "Date Account Parent Name Account Name Account .5", "Date Account Parent Name Account Name Account .6", "Date Account Parent Name Account Name Account .7", "Date Account Parent Name Account Name Account .8", "Date Account Parent Name Account Name Account .9", "Date Account Parent Name Account Name Account.10", "Date Account Parent Name Account Name Account.11", "Date Account Parent Name Account Name Account.12", "Date Account Parent Name Account Name Account.13", "Date Account Parent Name Account Name Account.14", "Date Account Parent Name Account Name Account.15", "Date Account Parent Name Account Name Account.16", "Date Account Parent Name Account Name Account.17", "Date Account Parent Name Account Name Account.18", "Date Account Parent Name Account Name Account.19", "Date Account Parent Name Account Name Account.20", "Date Account Parent Name Account Name Account.21", "Date Account Parent Name Account Name Account.22", "Date Account Parent Name Account Name Account.23", "Date Account Parent Name Account Name Account.24", "Date Account Parent Name Account Name Account.25", "Date Account Parent Name Account Name Account.26", "Date Account Parent Name Account Name Account.27", "Date Account Parent Name Account Name Account.28", "Date Account Parent Name Account Name Account.29", "Date Account Parent Name Account Name Account.30", "Date Account Parent Name Account Name Account.31", "Date Account Parent Name Account Name Account.32", "Date Account Parent Name Account Name Account.33"}),
#"Merged Columns" = Table.CombineColumns(#"Split Column by Delimiter",{"Date Account Parent Name Account Name Account.31", "Date Account Parent Name Account Name Account.32", "Date Account Parent Name Account Name Account.33"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
#"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Date Account Parent Name Account Name Account .1", "Date Account Parent Name Account Name Account .2", "Date Account Parent Name Account Name Account .4", "Date Account Parent Name Account Name Account .5", "Date Account Parent Name Account Name Account .6", "Date Account Parent Name Account Name Account .7", "Date Account Parent Name Account Name Account .8", "Date Account Parent Name Account Name Account .9", "Date Account Parent Name Account Name Account.10", "Date Account Parent Name Account Name Account.11", "Date Account Parent Name Account Name Account.13", "Date Account Parent Name Account Name Account.14", "Date Account Parent Name Account Name Account.15", "Date Account Parent Name Account Name Account.16", "Date Account Parent Name Account Name Account.17", "Date Account Parent Name Account Name Account.18", "Date Account Parent Name Account Name Account.20", "Date Account Parent Name Account Name Account.21", "Date Account Parent Name Account Name Account.22", "Date Account Parent Name Account Name Account.23", "Date Account Parent Name Account Name Account.24", "Date Account Parent Name Account Name Account.25", "Date Account Parent Name Account Name Account.26", "Date Account Parent Name Account Name Account.27", "Date Account Parent Name Account Name Account.29", "Date Account Parent Name Account Name Account.30"}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Removed Columns", {{"Date Account Parent Name Account Name Account Co", type date}}, "en-US"),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type with Locale",{{"Date Account Parent Name Account Name Account Co", "Date"}, {"Date Account Parent Name Account Name Account .3", "Account Parent Name"}, {"Date Account Parent Name Account Name Account.12", "Account Name"}, {"Date Account Parent Name Account Name Account.19", "Account Code"}, {"Date Account Parent Name Account Name Account.28", "Amount"}}),
#"Changed Type with Locale1" = Table.TransformColumnTypes(#"Renamed Columns", {{"Amount", Int64.Type}}, "en-US"),
#"Changed Type with Locale2" = Table.TransformColumnTypes(#"Changed Type with Locale1", {{"Merged", Int64.Type}}, "en-US"),
#"Added Custom" = Table.AddColumn(#"Changed Type with Locale2", "Year", each Date.Year([Date])),
#"Added Index" = Table.AddIndexColumn(#"Added Custom", "Sort", 1, 1, Int64.Type),
#"Grouped Rows" = Table.Group(#"Added Index", {"Year", "Account Name"}, {{"AllRows", each _, type table [Date=nullable date, Account Parent Name=nullable text, Account Name=nullable text, Account Code=nullable text, Amount=nullable number, Merged=nullable number, Year=number, Index=number]}}),
#"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([AllRows], "Index", 1, 1, Int64.Type)),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.1", each Table.AddIndexColumn([Custom], "Index0", 0, 1, Int64.Type)),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"Custom.1"}),
#"Expanded Custom.1" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom.1", {"Date", "Account Parent Name", "Account Name", "Account Code", "Amount", "Merged", "Year", "Sort", "Index", "Index0"}, {"Date", "Account Parent Name", "Account Name", "Account Code", "Amount", "Merged", "Year", "Sort", "Index", "Index0"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom.1",{{"Date", type date}, {"Account Parent Name", type text}, {"Account Name", type text}, {"Account Code", type text}, {"Amount", Int64.Type}, {"Merged", Int64.Type}, {"Sort", Int64.Type}, {"Index", Int64.Type}, {"Index0", Int64.Type}, {"Year", Int64.Type}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Index0", "Year", "Account Name"}, #"Changed Type", {"Index", "Year", "Account Name"}, "Expanded Custom.1", JoinKind.LeftOuter),
#"Expanded Expanded Custom.1" = Table.ExpandTableColumn(#"Merged Queries", "Expanded Custom.1", {"Amount"}, {"Amount.1"}),
#"Sorted Rows" = Table.Sort(#"Expanded Expanded Custom.1",{{"Sort", Order.Ascending}}),
#"Replaced Value" = Table.ReplaceValue(#"Sorted Rows",null,0,Replacer.ReplaceValue,{"Amount.1"}),
#"Added Custom3" = Table.AddColumn(#"Replaced Value", "Required Output", each [Amount]-[Amount.1], Int64.Type),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom3",{"Merged", "Year", "Sort", "Index", "Index0", "Amount.1"})
in
#"Removed Columns1"

Consider only the red ones because black steps where needed to achieve your starting table.

If you have problem in some steps i will explain each step.

If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution !

[rashidanwar]

Thank you @serpiva64!

Can you please share the PBI file? Actually when I paste your code in the query editor, its giving an error. 

[serpiva64 · Accepted solution]

Hi,

try this

https://studiopiva-my.sharepoint.com/:u:/g/personal/sergio_studiopiva_onmicrosoft_com/EdWnEyAXjtdKsf...

I think your problems are on local