17 Replies Latest reply: Nov 27, 2013 5:34 AM by Andrijan Chestnyh RSS

    Inventory and revenue calculation in a single document

      Hi


      Let’s say we have the following tables in the document:

       

      FactInventory:

      load * Inline [

      DateKey,      InventoryMovement,      ProductKey

      20131101, 3, 1

      20131101, 2, 2

      20131102, -1, 1

      ];

       

      FactSales:

      load * Inline [

      DateKey, Revenue, ProductKey

      20131101, 20 , 1

      20131101, 35, 2

      20131102, 15, 1  

      ];

       

       

      DimAsOfDate:

      load * Inline [

      DateKey, AsOfDateKey

      20131101, 20131101

      20131101, 20131102

      20131102, 20131102

      ];

       

      DimProduct:

      load * Inline [

      ProductKey, ProductName

      1, Product 1

      2, Product 2

      ];

       

       

      The problem is that we have to create single pivot showing something like


      but as you see the revenue is not correct

      Or another version

      and now inventory is showing only movements

       

      The data structure is:

       

      We were trying to unite two fact tables into one or change the structure in the orther way but still have no result.

      Any idea is welcome.