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/ 

[Mariusz]

Hi @nexami 

How often the stock is updated, is it once for every product?

Best Regards,
Mariusz

Please feel free to connect with me.

[nexami]

hello @Mariusz glad to hear from you, well the stock updates regularly having the following formulation:

Stock = Opening Stock + Purchase - Sale

Please check attached output example

[Mariusz]

@nexami 

Is opening stock only one of value for every sku?

Best Regards,
Mariusz

Please feel free to connect with me.

[nexami]

each SKU unique ID will have its own Stock value.

[Mariusz]

Hi @nexami 

Sorry, will it appear only one in a dataset for every sku and the rest need to be calculated?

Best Regards,
Mariusz

Please feel free to connect with me.

[nexami]

Hi @Mariusz look at the provided screenshot, stock is calculated on weekly basis.

also not 12/ 13 / 14 / 15 are week numbers (just to avoid confusion).

[Mariusz]

Hi @nexami 

Please see the below output based on the original data sample provided.

Please see the below M expression I used to Pivot the data on transaction column and later creatred a running total for Purchase - sales to be deducted of the stock value for every line. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnQytTBX0lEyMjC0BFKGRkAiJD8XSAaUFiVnJBangiUNlGJ18CkOTswBKTQ0xarOGJuhZtjNNMYwk7DlJfnJ2SAhMxxKTbDZb0pILdR+E5DCWAA=", 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),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom", each List.Sum(
            Table.AddColumn( 
                Table.SelectRows( 
                    #"Added Custom1", let _cmt = [CMT], _earWeek = [YearWeek] in each [CMT] = _cmt and [YearWeek] <= _earWeek 
                ),
                "var", each [Sale]- [Purchase]
            )[var]
        )),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom2", 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"

Sales and purchase will be normal sum aggregation where Last Stock is as below.

Last Stock = 
VAR _maxMonth = MAX( test[Week] )
VAR _maxYear = MAX( test[Year] )
RETURN 
CALCULATE(
    SUM( test[Stock] ),
    test[Week] = _maxMonth && 
    test[Year] = _maxYear
)

Due to complicated M expression and the size of your data this will perform rather slow.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[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/ 

[Mariusz]

Hi @ImkeF 

I'm looking forward to read it, thanks.

Best Regards,
Mariusz

Please feel free to connect with me.

[nexami]

@Mariusz can you please send me the excel file as attachment having the code written in it as i tried to put up the codes under query but i think its not what it was suppose to be.

[ImkeF · Accepted solution]

Hi @nexami ,

if you want to learn how to integrate M code into your own solution, this video might help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/Power-BI-Forum-Help-How-to-integrate-M-c...

[Mariusz]

Hi @nexami 

Please see the below link.

https://drive.google.com/file/d/11VxbtLymDOKLOeEyfdBYfXhlgVWAxLIJ/view?usp=sharing

Best Regards,
Mariusz

Please feel free to connect with me.

[nexami]

@Mariusz your provided codes are working fine except one glitch. Stock is only calculated on the basis of Week column and it does not function if other filters are applied. For example CMT or Customer.

Adding more, it should also work when year, month, quarter filters are applied.

Can you please modify the code and share

[Mariusz]

Hi @nexami 

Are you referring to M code or DAX code?
Where do you need apply the filters is it in query editor or data model?

Please can you give an example of the code not working and show what outcome tha you expect.

Best Regards,
Mariusz

Please feel free to connect with me.

[nexami]

M code.

see int he screenshots above, where i've increased the purchases / sales in each row, but after refreshing the query page, nothing happening.

[Mariusz]

Hi @nexami 

Can you copy from excel and paste in here, so its easier to work with data?

Many Thanks

Mariusz

[nexami]

SKU Transaction Week13 Week14 Week15
AB504 Purchase 200 60 50
AB504 Sales 15 100 450
AB504 Stock 1600 ???? ????

[nexami]

[nexami]

Anyone?
@Mariusz or else...

[nexami]

Got it.. Its perfect
Now tell me how to connect this code with the actual database of 300k records