Forum Discussion

JoseRovira's avatar
JoseRovira
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...
BI & Data Analysis
Charting
excel
Formulas and Functions
power bi
  • SergeiBaklan's avatar
    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.

     

mathetes's avatar
mathetes
Gold Contributor
Jul 24, 2023

JoseRovira 

 

You've had 50 views and counting with no reply. I suspect it's because we need more to work with.

 

It sounds as if you may have a workbook started with the data. Seeing that would be far more helpful than a reference to another thread with a chart (and data) that aren't directly relevant. Working with your actual data just makes so much more sense.

 

Post it here, or if you are not able to do that, post it on OneDrive or GoogleDrive with a link pasted here that grants access.

Resources