Forum Discussion
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 compare the month of May to July I can select May and also July. I also don't care about the type of fruit/inventory just the total amount per day. The empty date on the date sold would be because it hasn't been sold, so it technically would be today's date. For the example sheet attached the chart would go like so:
On 1/11/23 there would be a + $1.00 and a -$1.00 so 0 Total amount. Then it would stay on zero all the way until 3/25/23 where $2.24 will be added, then every day after that until the 4/5/23 it would be $2.24. On 4/5/23 the oranges where sold so it would go to $0, but Banas were bought and it would go back up to $4.92 then all the way to 7/20/23 the grapes would be added for a total of $40.92 so on and so on.
I would like for this to be automatic as I keep adding rows.
So far I used this as reference: https://techcommunity.microsoft.com/t5/excel/chart-of-expenses-over-time/m-p/175477/highlight/false#M4851
And I would like the same thing, just not amortized.
Thank you in advance,
Jose
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.
11 Replies
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.
- Thank you for your response! Could you further explain the process? I can't seem to make it work.
On which Excel version/platform you are? Or other words, is what Excel for Windows Desktop and do you have Power Pivot activated?
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.
