none
Power Query / Power Pivot - stock calculation RRS feed

  • Question

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

    SKU | 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 more than 500,000 rows with all the SKU and a couple of year's worth of transactions for all customers.

    This is what it looks like right now:

    SKU 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 SKU 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).

    Friday, August 16, 2019 7:06 AM