Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply

Take last non value for next calculation

i have 3 columns (distributor_code, Division, Prim Item Code) for each combination of these i have a open stock and if you see below you will also find the repeacting prim item code, i want to do a calculation where if i have a openstock for a particular prim item code(which is provided in start of the month) the final answer for 01-01-2021 for code 2000169 can also be used in 02-01-2021 for code 2000169 where i dont have a open stock.
so the calcultion will go like acc to me. calculation(open stock+prim sales-sec sales) for 2000169 on date 01-01-2021 and if we dont have open stock for same combination of (distributor_code, Division, Prim Item Code) then it should take the last latest value as a open stock

Distributor_CodeDivisionPrim Item CodeDateOpen StockPrim SalesSec Sales
1100794Retail200016901-01-20214588.700
1100794Retail200017701-01-20212326.400
1100794Retail200017201-01-20211412.1600
1100794Retail200002301-01-2021111.900
1100794Retail200002302-01-2021000.003
1100794Retail200016902-01-2021000.43
1100794Retail200017202-01-2021000.195
1100794Retail200017702-01-2021000.514
1100794Retail200002303-01-2021000.092
1100794Retail200016903-01-2021000.2
1100794Retail200017203-01-2021000.291

@

1 ACCEPTED SOLUTION
ronrsnfld
Super User
Super User

Here is a Power Query solution, annotated with comments

let

//change next line to reflect your actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table18"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Distributor_Code", Int64.Type}, {"Division", type text}, {"Prim Item Code", Int64.Type}, {"Date", type date}, {"Open Stock", type number}, {"Prim Sales", Int64.Type}, {"Sec Sales", type number}}),

//add index column to be able to sort back to original order
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),

//Group by "dist code", "division", and "prim item code"
// then add a column to each subgroup for the closing stock based on rules in your question.
    #"Grouped Rows" = Table.Group(#"Added Index", {"Distributor_Code", "Division", "Prim Item Code"}, {
        
        {"add cs", (t)=>
            Table.FromColumns(
                Table.ToColumns(t) & 
                {List.Generate(
                    ()=>[cs=t[Open Stock]{0} + t[Prim Sales]{0} - t[Sec Sales]{0}, idx = 0],
                    each [idx] < Table.RowCount(t),
                    each [cs= let 
                                Open = if t[Open Stock]{[idx]+1} = 0 then [cs] else t[Open Stock]{[idx]+1}
                             in 
                                Open + t[Prim Sales]{[idx]+1} - t[Sec Sales]{[idx]+1}, idx = [idx]+1],
                    each [cs])}, {"Distributor_Code", "Division", "Prim Item Code", "Date", 
            "Open Stock", "Prim Sales", "Sec Sales", "Index", "Closing Stock"})}          
            }),

//expand the grouped table
//sort back to original order
//remove the Index column
    #"Expanded add cs" = Table.ExpandTableColumn(#"Grouped Rows", "add cs", {"Date", "Open Stock", "Prim Sales", "Sec Sales", "Index", "Closing Stock"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded add cs",{{"Date", type date},{"Open Stock", type number}, {"Prim Sales", Int64.Type}, {"Sec Sales", type number}, {"Index", Int64.Type}, {"Closing Stock", type number}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"

 

ronrsnfld_0-1663702762789.png

 

 

View solution in original post

5 REPLIES 5
ronrsnfld
Super User
Super User

Here is a Power Query solution, annotated with comments

let

//change next line to reflect your actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table18"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Distributor_Code", Int64.Type}, {"Division", type text}, {"Prim Item Code", Int64.Type}, {"Date", type date}, {"Open Stock", type number}, {"Prim Sales", Int64.Type}, {"Sec Sales", type number}}),

//add index column to be able to sort back to original order
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),

//Group by "dist code", "division", and "prim item code"
// then add a column to each subgroup for the closing stock based on rules in your question.
    #"Grouped Rows" = Table.Group(#"Added Index", {"Distributor_Code", "Division", "Prim Item Code"}, {
        
        {"add cs", (t)=>
            Table.FromColumns(
                Table.ToColumns(t) & 
                {List.Generate(
                    ()=>[cs=t[Open Stock]{0} + t[Prim Sales]{0} - t[Sec Sales]{0}, idx = 0],
                    each [idx] < Table.RowCount(t),
                    each [cs= let 
                                Open = if t[Open Stock]{[idx]+1} = 0 then [cs] else t[Open Stock]{[idx]+1}
                             in 
                                Open + t[Prim Sales]{[idx]+1} - t[Sec Sales]{[idx]+1}, idx = [idx]+1],
                    each [cs])}, {"Distributor_Code", "Division", "Prim Item Code", "Date", 
            "Open Stock", "Prim Sales", "Sec Sales", "Index", "Closing Stock"})}          
            }),

//expand the grouped table
//sort back to original order
//remove the Index column
    #"Expanded add cs" = Table.ExpandTableColumn(#"Grouped Rows", "add cs", {"Date", "Open Stock", "Prim Sales", "Sec Sales", "Index", "Closing Stock"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded add cs",{{"Date", type date},{"Open Stock", type number}, {"Prim Sales", Int64.Type}, {"Sec Sales", type number}, {"Index", Int64.Type}, {"Closing Stock", type number}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"

 

ronrsnfld_0-1663702762789.png

 

 

Hi @v-jingzhang 

Thank you so much for the solution, can you please share the PBIX file too, it would be a bit more helpful for me❤️.

You don't need a PBIX file.  As I wrote, all you need to do is replace the lines in your own file after `#"Changed Type#` with those lines in the MCode I supplied.

Or just paste the code below into a blank query using the Advanced Editor in your PBI power query, and it will reproduce everything including the table.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldBBCgIxDAXQu3Q9lv/TtJ1cw+3QhQsXgkvvj9UiyNgpFRJICw/ys22OBLKpW9z5+rjc7nUQAExWJ/BUSyCsD43r6vPr991lOcY577EESV7nsOwxleKZZjQk/GjS2x9Wviw+7YEw3Lrdq291TFvgPqXFiUv3baROxA39uCYTcft2LFvaA2l0pTwB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Distributor_Code = _t, Division = _t, #"Prim Item Code" = _t, Date = _t, #"Open Stock" = _t, #"Prim Sales" = _t, #"Sec Sales" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Distributor_Code", Int64.Type}, {"Division", type text}, {"Prim Item Code", Int64.Type}, {"Date", type date}, {"Open Stock", type number}, {"Prim Sales", Int64.Type}, {"Sec Sales", type number}}),
//add index column to be able to sort back to original order
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),

//Group by "dist code", "division", and "prim item code"
// then add a column to each subgroup for the closing stock based on rules in your question.
    #"Grouped Rows" = Table.Group(#"Added Index", {"Distributor_Code", "Division", "Prim Item Code"}, {
        
        {"add cs", (t)=>
            Table.FromColumns(
                Table.ToColumns(t) & 
                {List.Generate(
                    ()=>[cs=t[Open Stock]{0} + t[Prim Sales]{0} - t[Sec Sales]{0}, idx = 0],
                    each [idx] < Table.RowCount(t),
                    each [cs= let 
                                Open = if t[Open Stock]{[idx]+1} = 0 then [cs] else t[Open Stock]{[idx]+1}
                             in 
                                Open + t[Prim Sales]{[idx]+1} - t[Sec Sales]{[idx]+1}, idx = [idx]+1],
                    each [cs])}, {"Distributor_Code", "Division", "Prim Item Code", "Date", 
            "Open Stock", "Prim Sales", "Sec Sales", "Index", "Closing Stock"})}          
            }),

//expand the grouped table
//sort back to original order
//remove the Index column
    #"Expanded add cs" = Table.ExpandTableColumn(#"Grouped Rows", "add cs", {"Date", "Open Stock", "Prim Sales", "Sec Sales", "Index", "Closing Stock"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded add cs",{{"Date", type date},{"Open Stock", type number}, {"Prim Sales", Int64.Type}, {"Sec Sales", type number}, {"Index", Int64.Type}, {"Closing Stock", type number}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"

 

v-jingzhang
Community Support
Community Support

Hi @UjjawalTyagi518 

 

Is this what you want?

vjingzhang_0-1663654753126.png

 

Closing Stock is a calculated column with below code. 

Closing Stock = 
CALCULATE (
    SUM ( 'Table'[Open Stock] ) + SUM ( 'Table'[Prim Sales] )
        - SUM ( 'Table'[Sec Sales] ),
    ALLEXCEPT (
        'Table',
        'Table'[Distributor_Code],
        'Table'[Division],
        'Table'[Prim Item Code]
    ),
    'Table'[Date] <= EARLIER ( 'Table'[Date] )
)

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Hi @v-jingzhang @Jing, thanks for the answer it works properly, but actually i want to do this calculation in power query. Can you please suggest the solution for power query

Thanks

 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors