How to calculate the daily inventory?

[Gjakova]

Hi there, I am struggling with the following issue:

I need to calculate the inventory for each date which is based on the result of previous day. Thus I for example on January 1st I have 10 iPhones left and my store purchases 5 more, I sell 9 iPhones on that same day which leaves me with 10 + 5 - 9 = 6 iPhones. On January 2nd I start with 6 iPhones, I buy 3 and sell 7 which leaves me with 6 + 3 - 7 = 2 iPhones, on January 3rd I thus start with 2 iPhones and the process gets repeated. The table looks like this:

Product	Date	Base Stock	Opening	Purchased	Sold	Closing Inventory
iPhone	January 1st	10	10	5	9	6
iPhone	January 2nd	/	6	3	7	2
iPhone	January 3rd	/	2	20	5	17
Apple Watch	January 1st	70	70	5	40	35

My model looks like this:

I was thinking of creating one table, but the problem is that it would be MORE THAN 50 MILLION ROWS! in my real dataset.

Basically, when I select a product and a date with my slicer, I want to see how many products I have left on a certain date.

My Basic Stock table has the starting stock on January 1st for all my products, I only use that once (to see how many products I have when I start counting (only 01-01-20xx). So January 1st, 2020 looks at Basic Stock, but January 1st, 2021 looks at December 31, 2020.

Furthermore, the purchase of certain items can differ with selling it on a certain item. So if I buy 5 iPhones on April 1st, 2020, it does not mean that I sell thatm on April 1st, 2020 but I could sell them on April 8, 2020 or another date.

The main problem for me is how to calculate with a measure what my Closing Inventory is, especially when the Opening of a products needs to look at the Closing Inventory of the previous date.

Here is a sample of my PBIX: https://www.dropbox.com/s/nkpu4vxj2hvirg8/Inventory%20Issue.pbix?dl=0

Happy to hear how this would be best solved!

[v-deddai1-msft · Accepted solution]

Hi @Gjakova ,

First disable all relationship between your fact table to calendar table. Then you can use the following measure:

Purchased = CALCULATE(SUM(Purchases[Quantity]),FILTER(Purchases,Purchases[Purchase Date]<=MAX('Calendar'[Date])))

Sold = CALCULATE(SUM(Sales[Quantity]),FILTER(Sales,Sales[Sales Date]<=MAX('Calendar'[Date]))) 

Closing Inventory = MAX('Basic Stock'[Quantity])+[Purchased]-[Sold]

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Gjakova]

Hi @v-deddai1-msft thank you! I will try it ASAP and let you know. One question though... what is the reason that the relationship between my calendar and fact table should be disabled? Kind regards!