How to get last month value as initiate value of this month by power query

[NBU_FFF]

Hello Community, 

I'm trying to get a solution by using power query for my painful case.

My source table to be:

My target output to be:

Again You can see the "Month_Begin" value as yellow high-lighted is from last month "Month_End". And "Month_End" part is "Month_Begin"+"In"-"Out".

I attached my sample pbix file for your eay updating.

https://1drv.ms/u/s!AhkI23dIWO7yiUscMYE6vuXTWTg0?e=BJpBf4

[lbendlin · Accepted solution]

see attached

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZCxDcAgDAR3cU2BDcShTNZA7L9GeCjAkQtOoAOdRWskUXJkCvSMpdhwjCD18LcyLY7J2ne/xZ3i2DQtqNuK7ebB6tjVLSBbfYQvMDl6lRU8Bks2fYPq6NWuYLV6txkfJuzo2WZckjFa/wA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, WH = _t, Division = _t, In = _t, Out = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", Int64.Type}, {"WH", type text}, {"Division", Int64.Type}, {"In", Int64.Type}, {"Out", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Month_In", (k)=> let s=Table.SelectRows(#"Changed Type",each [WH]=k[WH] and [Division]=k[Division] and [Month]<k[Month]) in List.Sum(s[In])-List.Sum(s[Out]),Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Month_Out",  (k)=> let s=Table.SelectRows(#"Changed Type",each [WH]=k[WH] and [Division]=k[Division] and [Month]<=k[Month]) in List.Sum(s[In])-List.Sum(s[Out]),Int64.Type)
in
    #"Added Custom1"

[dufoq3 · Accepted solution]

Buffered using List.Generate

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZCxDcAgDAR3cU2BDcShTNZA7L9GeCjAkQtOoAOdRWskUXJkCvSMpdhwjCD18LcyLY7J2ne/xZ3i2DQtqNuK7ebB6tjVLSBbfYQvMDl6lRU8Bks2fYPq6NWuYLV6txkfJuzo2WZckjFa/wA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, WH = _t, Division = _t, In = _t, Out = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Month", Int64.Type}, {"Division", Int64.Type}, {"In", type number}, {"Out", type number}}),
    #"Added Index" = Table.AddIndexColumn(ChangedType, "Index", 0, 1, Int64.Type),
    GroupedRows = Table.Group(#"Added Index", {"WH", "Division"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"All", each _, type table [Month=nullable text, WH=nullable text, Division=nullable text, In=nullable text, Out=nullable text]}}),
    Ad_CalcColumns = Table.AddColumn(GroupedRows, "CalcColumns", each 
        Table.FromRecords(
                List.Generate(
                        ()=> [ 
                                x = 0, v_all = Table.Buffer(Table.Sort([All], {{"Month", Order.Ascending}})),
                                v_in = v_all[In]{x}, v_out = v_all[Out]{x},
                                v_monthBegin = 0, v_monthEnd = v_monthBegin + v_in - v_out
                                ],
                        each [ x ] < Table.RowCount([v_all]),
                        each [ 
                                x = [x]+1,
                                v_all = [v_all],
                                v_in = [v_all][In]{x},
                                v_out = [v_all][Out]{x},
                                v_monthBegin = [v_monthEnd],
                                v_monthEnd = v_monthBegin + v_in - v_out
                                ],
                        each [ [v_monthBegin], [v_monthEnd] ]
                ),
                type table [v_monthBegin = Number.Type, v_monthEnd = Number.Type]
        ), type table
    ),
    Merged = Table.AddColumn(Ad_CalcColumns, "Custom", each Table.FromColumns(
    Table.ToColumns([All]) & Table.ToColumns([CalcColumns]), Table.ColumnNames([All]) & Table.ColumnNames([CalcColumns]))),
    Result = Table.Combine(Merged[Custom]),
    Sorted = Table.Sort(Result,{{"Index", Order.Ascending}}),
    RemovedColumns = Table.RemoveColumns(Sorted,{"Index"})
in
    RemovedColumns

[NBU_FFF]

A good solution. Thanks. 

[ronrsnfld]

If you only have two "WH"'s you can just add a column that is offset from the Month_End by two.

However, if you might have a variable number of distinct "WH"'s, I suggest you 

 - Start with just the first four columns

 - Group by WH

 - Then use List.Generate to aggregate by generating the individual rows, including the computation of both Month_Begin and Month_End.

let
    Source = Excel.CurrentWorkbook(){[Name="Table25"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", Int64.Type}, {"WH", type text}, {"In", Int64.Type}, {"Out", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"WH"}, {
        {"Month End/Begin", (x)=> let t = Table.Sort(x,{{"Month",Order.Ascending}}) in 
            List.Generate(
                ()=>[m=t[Month]{0}, in=t[In]{0}, out=t[Out]{0}, mb=0, me=t[In]{0}-t[Out]{0}, idx=0],
                each [idx] < Table.RowCount(t),
                each [m=t[Month]{[idx]+1}, in=t[In]{[idx]+1}, out=t[Out]{[idx]+1},mb=[me], me = [me] + t[In]{[idx]+1} - t[Out]{[idx]+1}, idx=[idx]+1],
                each Record.FromList(
                        {[m],[in],[out], [mb],[me]},
                        {"Month","In","Out", "Month Begin","Month End"}))
        }}),
    #"Expanded Month End/Begin" = Table.ExpandListColumn(#"Grouped Rows", "Month End/Begin"),
    #"Expanded Month End/Begin1" = Table.ExpandRecordColumn(#"Expanded Month End/Begin", "Month End/Begin", {"Month", "In", "Out", "Month Begin", "Month End"}, {"Month", "In", "Out", "Month Begin", "Month End"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Month End/Begin1",{"Month", "WH", "In", "Out", "Month Begin", "Month End"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Month", Order.Ascending}, {"WH", Order.Ascending}})
in
    #"Sorted Rows"

Data

Results

[NBU_FFF]

Thanks for help. My actual situation seemed one more column(Division) than above and I don't know how proper group shold be:

My source table to be:

My target output to be:

Again You can see the "Month_Begin" value as yellow high-lighted is from last month "Month_End". And "Month_End" part is "Month_Begin"+"In"-"Out".

I attached my sample pbix file for your eay updating.

https://1drv.ms/u/s!AhkI23dIWO7yiUscMYE6vuXTWTg0?e=BJpBf4

Thanks again.

[ronrsnfld]

You just group by WH and Division.

Because you are now grouping differently, I added an Index column to retain the sort order.

let
    Source = Excel.CurrentWorkbook(){[Name="Table25"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", Int64.Type}, {"WH", type text}, {"In", Int64.Type}, {"Out", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    
    #"Grouped Rows" = Table.Group(#"Added Index", {"WH","Division"}, {
        {"Month End/Begin", (t)=>
            List.Generate(
                ()=>[i=t[Index]{0}, m=t[Month]{0}, in=t[In]{0}, out=t[Out]{0}, mb=0, me=t[In]{0}-t[Out]{0}, idx=0],
                each [idx] < Table.RowCount(t),
                each [i=t[Index]{[idx]+1}, m=t[Month]{[idx]+1}, in=t[In]{[idx]+1}, out=t[Out]{[idx]+1},mb=[me], me = [me] + t[In]{[idx]+1} - t[Out]{[idx]+1}, idx=[idx]+1],
                each Record.FromList(
                        {[m],[in],[out], [mb],[me],[i]},
                        {"Month","In","Out", "Month Begin","Month End","Index"}))}}),

    #"Expanded Month End/Begin" = Table.ExpandListColumn(#"Grouped Rows", "Month End/Begin"),
    #"Expanded Month End/Begin1" = Table.ExpandRecordColumn(#"Expanded Month End/Begin", "Month End/Begin", {"Month", "In", "Out", "Month Begin", "Month End", "Index"}, {"Month", "In", "Out", "Month Begin", "Month End", "Index"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Month End/Begin1",{{"Index", Order.Ascending}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",{"Month", "WH", "In", "Out", "Month Begin", "Month End"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Index"})
in
    #"Removed Columns"

Data

Result

[lbendlin · Accepted solution]

see attached

[NBU_FFF]

Looks Perfect by DAX, but is there any way to do that power Query?

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZCxDcAgDAR3cU2BDcShTNZA7L9GeCjAkQtOoAOdRWskUXJkCvSMpdhwjCD18LcyLY7J2ne/xZ3i2DQtqNuK7ebB6tjVLSBbfYQvMDl6lRU8Bks2fYPq6NWuYLV6txkfJuzo2WZckjFa/wA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, WH = _t, Division = _t, In = _t, Out = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", Int64.Type}, {"WH", type text}, {"Division", Int64.Type}, {"In", Int64.Type}, {"Out", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Month_In", (k)=> let s=Table.SelectRows(#"Changed Type",each [WH]=k[WH] and [Division]=k[Division] and [Month]<k[Month]) in List.Sum(s[In])-List.Sum(s[Out]),Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Month_Out",  (k)=> let s=Table.SelectRows(#"Changed Type",each [WH]=k[WH] and [Division]=k[Division] and [Month]<=k[Month]) in List.Sum(s[In])-List.Sum(s[Out]),Int64.Type)
in
    #"Added Custom1"

[NBU_FFF]

@lbendlin Perfect Solution. Thanks for making my life easy! 

[lbendlin]

What do you need these columns for?  This can be done in Power BI with implicit measures, without coding.

[NBU_FFF]

@lbendlin , this is an inventory management case. I want to get the month end data and use it for next month start dta. I have uploaded the file and pls give your measures on that directly if possible. Thanks.

[speedramps]

Please provide data as a table (not a picture) so that we can import it.

Must it be a Power Query solution?

Will a DAX calculated column suffice ?

Or a DAX measure?

[NBU_FFF]

@speedramps , for  sure any solution is welcome. I have uploaded my file and hopefully you can help on that.