Power Query / Power Pivot - stock calculation

[nexami]

I have a table in Power query, which besides other fields has the following key fields:

CMT | Year | Week | Customer | Transaction | Value

AB587 | 2019 | 12 | Tom | Purchase | 200

AB587 | 2019 | 12 | Tom | Sale | 15

AB587 | 2019 | 13 | Tom | Purchase | 60

AB587 | 2019 | 13 | Tom | Sale | 100

AB587 |2019 | 12 | Tom | Stock | 1600

AB587 | 2019 | 14 | Tom | Purchase | 50

AB587 | 2019 | 14 | Tom | Sale | 450

This is a table with about 300,000 rows with all the CMT and a couple of year's worth of transactions for all customers.

This is what it looks like right now:

CMT and Transaction as rows, and Weeks as columns, with Value as values, and customer as report filter.

the pivot table obviously shows what's in the raw data. What I want to do is to have the pivot table calculate the Stock for Weeks 13, 14 and so on. In the above example, I would expect the Stock in Week 13 to have 1600-100+60=1560, and Week 14 Stock to have 1560-450+50=1160, and so on.

Basically the pivot table should be projecting the stock in hand. I also want the pivot table to be able to do that when the CMT is removed from the rows and replaced by Customer or any other such combination. One more thing is that if the user brings in "months" instead of Weeks, the Stock should show the value of the last week of each month (the raw data has a month next to week in each row).

[ImkeF · Accepted solution]

If you're worried about performance, you can use this trick to substantially improve speed for a case like this: https://www.thebiccountant.com/2017/05/29/performance-tip-partition-tables-crossjoins-possible-power...

Your code would look like so:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnQytTBX0lEyMjC0BFKGRkAiJD8XSAaUFiVnJBangiUNlGJ18CkOTswBKTQ0xarOGJuhZtjNNMYwk7DlJfnJ2SAhMxxKTbDZb0pILdR+EySFFkSEFCHV8KDCrhBHWBFSjAgsgvYjhRZ2tTiCi5BiRHgBVcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [CMT = _t, Year = _t, Week = _t, Customer = _t, Transaction = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CMT", type text}, {"Year", Int64.Type}, {"Week", Int64.Type}, {"Customer", type text}, {"Transaction", type text}, {"Value", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Transaction]), "Transaction", "Value"),
    #"Added Custom1" = Table.AddColumn(#"Pivoted Column", "YearWeek", each [Year] * 100 + [Week], Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"CMT"}, {{"All", (Partition) => Table.AddColumn(Partition, "Custom", each List.Sum(
            Table.AddColumn( 
                Table.SelectRows( 
                    Partition, let  _earWeek = [YearWeek] in each [YearWeek] <= _earWeek 
                ),
                "var", each [Sale]- [Purchase]
            )[var]
        )) }}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Year", "Week", "Customer", "Purchase", "Sale", "Stock", "YearWeek", "Custom"}, {"Year", "Week", "Customer", "Purchase", "Sale", "Stock", "YearWeek", "Custom"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded All", each [Stock], each [Stock] + [Custom],Replacer.ReplaceValue,{"Stock"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Stock"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filled Down",{{"Stock", Int64.Type}}),
    #"Replaced Value1" = Table.ReplaceValue(#"Changed Type1",each [Stock], each [Stock] - [Custom],Replacer.ReplaceValue,{"Stock"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value1",{{"Stock", Int64.Type}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type2",{"CMT", "Year", "Week", "Customer", "Purchase", "Sale", "Stock"})
in
    #"Removed Other Columns"

You'll find some more performance tricks here: https://www.thebiccountant.com/speedperformance-aspects/ 

[nexami]

@Mariusz also please confirm, once integrated with the actual database, which has multiples of CMT and different customers, the stock calculations will go on whenever filters are applied

[ImkeF]

Hi @nexami ,

the video should have given you some ideas for your first steps, so what do you have so far?

I'm happy to fill in the blanks.

[nexami]

@ImkeF moving on to the next level of my main quesiton.

i've a large database of around 300k records, and it keeps on adding up with purchases, sales, customers, etc.

so with the existing database, how am i suppose to integrate the MCODE. i can call up the database location from the Power Query, what i'm confused with is how does the MCODE provided by @Mariusz will work with that specific databse.

[nexami]

@ImkeF @Mariusz 

here's something i just tried, using the MCODE, infact i dind't touched the MCODE at all 🙂

what i did is manually increased the number of records with different CMT / Customer Name / Sale and Purchase numbers.

and upon refreshing the query, i had the surprised results 🙂

[nexami]

@ImkeF @Mariusz 

Now the only thing remaining is how to connect this MCODE query with the actual master database, 

all i need is to join the query with the database so it captures the information automatically and returns the filtered details.

[nexami]

@ImkeF the video tutorial is awesome, something i had been looking forward to learn for quiet sometime but couldn't found a lead.

the way you've explained it is brilliant specially taking along with 4 different examples.

thank you so much for your help

[nexami]

Looks amazing, I'll apply the codes and will share results

[Mariusz]

Hi @nexami 

The original post was showing stock record only for the 12th, I was under impression that you need this to be calculated for the rest of days. is my thinking incorrect?

Best Regards,
Mariusz

Please feel free to connect with me.

[nexami]

i'm sorry for not briefing it earlier, 12 / 13 / 14 / 15 are the week numbers.

secondly stock is required to be calculated along with each week purcahse and sale. even if there are none (zero purchase / zero sale).

[v-piga-msft]

Hi @nexami ,

I have a little confused about your logic firstly. If it is convenient, could you share your desired output?

---

This is what it looks like right now:

CMT and Transaction as rows, and Weeks as columns, with Value as values, and customer as report filter.

---

In addition, I have confused about your current output.  Do you have the output like below?

If not, could you share a screenshot  about your output.

Best  Regards,

Cherry

[nexami]

hello @v-piga-msft and thanks for your reply, please find attached output required

Stock = Operning Stock + Purchase - Sale.