Re: Inventory and revenue calculation in a single document
The numbers are correct.
Your association of DateKey '20131101' with AsOfDateKey '20131102' is going to cause everything from both dates to show up under 20131102 in the first pivot table. Thus 35 shows up for Product 2 on both days (because it's from 20131101) and 35 (20+15) shows up for Product 1 on day 2 because it adds them.
When you use the first DateKey, the pivot table is properly displaying the values for the dates and products.
Re: Re: Inventory and revenue calculation in a single document
The result in the two pivot table is not the one I'm tring to get.
It should look like this:
showing the revenue at that date (usual sum) and inventory by that date (accumulative sum from the beginning).
The association of DateKey and AsOfDateKey is done to achieve accumulative sum by the date for inventory. It can show correct numbers if we separate inventory and revenue to two different pivot tables and use DateKey for Revenue and AsOfDateKey for Inventory. But the goal is to make it in a single table where we can use just one date.