Hi Gurus,
Been cracking my head on this and wasn't able to find something related. Have a typical Supply Chain scenario where business have:
1) In-coming Purchase by Date
2) Forecast / Demand
We want Inventory Projection.
Eg.
On conventional Excel it's super straight forward i.e. Previous day Inventory + sum of current date column. However, it's mind blowing when trying to achieve on Power Query / Pivot.
If I use Power Pivot terms, I actually want something like this : Projection := Calculate (sum(value), Table.Date <= Date
Been cracking my head on this and wasn't able to find something related. Have a typical Supply Chain scenario where business have:
1) In-coming Purchase by Date
2) Forecast / Demand
We want Inventory Projection.
Eg.
| Initial | 5/14/2016 | 5/15/2016 | 5/16/2016 | 5/17/2016 | 5/18/2016 | 5/19/2016 | 5/20/2016 | 5/21/2016 | 5/22/2016 | 5/23/2016 | 5/24/2016 | 5/25/2016 | 5/26/2016 | 5/27/2016 | ||
| Apple | Purchase | 350 | 200 | 200 | 200 | 200 | 200 | 200 | 200 | 200 | 200 | 200 | 200 | 200 | 200 | |
| Apple | Demand | -100 | -300 | -500 | -100 | |||||||||||
| Inventory projection | 0 | 250 | 450 | 650 | 550 | 750 | 950 | 1150 | 850 | 1050 | 1250 | 1350 | 1550 | 1750 | 1950 |
On conventional Excel it's super straight forward i.e. Previous day Inventory + sum of current date column. However, it's mind blowing when trying to achieve on Power Query / Pivot.
If I use Power Pivot terms, I actually want something like this : Projection := Calculate (sum(value), Table.Date <= Date