Copper Contributor

Jul 24, 2023

Solved

Amount of $ in inventory over time

Hello everyone, I would like to create a PivotChart that shows the current inventory in my warehouse (in $). I want to be able to see the total amount by day. For example: If I would like to comp...

Formulas and Functions

power bi

SergeiBaklan

Jul 24, 2023

JoseRovira

If something like this

one of variants could be

- load source table to data model, by Power Query in that sample

- in Power Pivot or by Power Query (lot of templates exist) add Date (aka Calendar) table to data model

- create relationships with source table

(only one of them will be shown as active)

- create DAX measures

Cost Amount:=SUM ( Query_Table1[Total Cost] )

Cost Delivered:=VAR lastInPeriod =
    MAX ( 'Calendar'[Date] )
VAR firstInPeriod =
    MIN ( 'Calendar'[Date] )
VAR lastDelivered =
    CALCULATE (
        MAX ( Query_Table1[Date delivered to Warehouse] ),
        ALL ( Query_Table1 )
    )
VAR lastSold =
    CALCULATE (
        MAX ( Query_Table1[Date Sold (out of warehouse)] ),
        ALL ( Query_Table1 )
    )
VAR lastInventory =
    MAX ( lastDelivered, lastSold )
VAR result =
    IF (
        firstInPeriod <= lastInventory,
        CALCULATE (
            [Cost Amount],
            'Calendar'[Date] <= lastInPeriod,
            Query_Table1[Date delivered to Warehouse] <> BLANK (),
            USERELATIONSHIP ( 'Calendar'[Date], Query_Table1[Date delivered to Warehouse] )
        )
    )
RETURN
    result

Cost Sold:=VAR lastInPeriod =
    MAX ( 'Calendar'[Date] )
VAR firstInPeriod =
    MIN ( 'Calendar'[Date] )
VAR lastDelivered =
    CALCULATE (
        MAX ( Query_Table1[Date delivered to Warehouse] ),
        ALL ( Query_Table1 )
    )
VAR lastSold =
    CALCULATE (
        MAX ( Query_Table1[Date Sold (out of warehouse)] ),
        ALL ( Query_Table1 )
    )
VAR lastInventory =
    MAX ( lastDelivered, lastSold )
VAR result =
    IF (
        firstInPeriod <= lastInventory,
        CALCULATE (
            [Cost Amount],
            'Calendar'[Date] <= lastInPeriod,
            Query_Table1[Date Sold (out of warehouse)] <> BLANK (),
            USERELATIONSHIP ( 'Calendar'[Date], Query_Table1[Date Sold (out of warehouse)] )
        )
    )
RETURN
    result

Inventory Amount:=[Cost Delivered] - [Cost Sold]

- use above to build PivotTable and/or PivotChart from data model.

Please check in attached file.

Example (1).xlsx258 KB

JoseRovira

Copper Contributor

Jul 24, 2023

Thank you for your response! Could you further explain the process? I can't seem to make it work.

SergeiBaklan

Diamond Contributor

Jul 24, 2023

JoseRovira

On which Excel version/platform you are? Or other words, is what Excel for Windows Desktop and do you have Power Pivot activated?

JoseRovira
Copper Contributor
Jul 24, 2023
Microsoft® Excel® for Microsoft 365 MSO (Version 2306 Build 16.0.16529.20164) 64-bit .

Power Pivot ribbon tab?
- SergeiBaklan
  Diamond Contributor
  Jul 24, 2023
  JoseRovira
  
  Yes, this one
  
  To simplify a bit, stay on any cell within your source table and from above tab on ribbon click Add to Data Model.
  
  Power Pivot will be opened.
  
  Here create new Date table as
  
  By default it takes dates for years for min and max dates from all existing table. In general nothing to do with it, the only you may Update Range (if you wish) after Calendar table is created.
  
  Next shift on Diagram View and create relationships
  
  drag one by one Date Delivered and Date Sold on Date field in Calendar table. Optionally after that from right click menu you may hide from client view both date fields in source table.
  
  Return back to data View and in the grid below the table create measures one by one using formula bar.
  
  Alternatively you may use Measures on ribbon
  
  Finally build aggregation within the grid using
  
  or Insert -> PivotTable / PivotChart on ribbon.
  - JoseRovira
    Copper Contributor
    Jul 25, 2023
    SergeiBaklan
    
    Thank you, that is very helpful. Now I'm having errors in the measures.
    
    Could you help me with this? The different names are just because I'm using them on a different sheet.

Forum Discussion

Amount of $ in inventory over time

Resources