Stock Ageing Analysis Using FIFO Calculation

Power BI Online Service
1

Hello clever people,

I have been struggling to create an Ageing view of the stock on hand in my two retail stores. I really wish Sam had covered this in the Inventory Showcase… hint hint)

I have the daily Stock on Hand (SOH) and Sales for each store by date and SKU, I also have the Dispatches from the DC that supplies the stores, I do not have a receipt of the stock when it arrives into the store so I will assume that the good reach the stock on the same day it is dispatched.

There was some stock on hand in the store on the date that my reporting starts (01/01/2020) and I will consider all this stock to be 1 day old on the 01/01/2020.

The result I am looking for is to populate a set of Age Buckets with the number of units and value of stock in each bucket. The buckets are 0-15 days old, 16-30 days old, 31 to 45 days old etc.

I have found many examples in SQL about how to achieve this but unfortunately, I have no SQL experience and I am sure that this can be created in DAX if someone could steer me in the right direction.

I have made a test dataset and placed it in the following location: https://drive.google.com/drive/folders/1LaCQk2WwaUl9Wskd-8PyfO0CNcVbvZEi?usp=sharing

Please let me know if you need any additional information to be able to assist.

Cheers,

Phil

2

Hi @PhilBowen,

i went to youtube and typed this inventory fifo method within power bi

Several videos came up that you might want to check out.

i hope this helps.
keith

3

Hi Keith,

Thanks for your prompt reply… as mentioned I have spent hours researching the solution, including watching several You Tube videos, but I have not found one that deals with the ageing of remaining SOH. Plenty about calculating COGS using FIFO or aged invoices, but I just cant get the result I am after, hence my Hail Mary pass onto the forum.

Cheers,

Phil

4

Hi @PhilBowen,

Maybe I’ve misunderstood or there is some issue with your data. Can you check the following for me?
I’ve zoomed in on ShipToID = R001 and SKU =1092700140

You have a starting balance of 1 on 1-1-2020 but also a Sales of 2 pieces that doesn’t get reflected in your SOH ( should all Sales on 1-1-2020 be discarded?) There is another Sale of 1 piece on 7-1-2020
Before the first Shipment of 5 pieces comes in on 8-1-2020…

If that is the case, is that also true for Shipments coming in on 1-1-2020, should they also be discarded?

Thanks!

5

Hi Melissa,

You sure do have a keen eye for detail!

I definitely should have explained myself better… the SOH is a closing figure for each day, so it is entirely possible that goods will be dispatched from the DC, arrive in store and be sold on the same day and never reflect in the SOH report.

Does that make sense?

Thank you very much for looking into this for me.

Cheers,

Phil

6

For FIFO calculation it’s important to have a record for each mutation but as you clarified that isn’t the case at the moment. To illustrate see the image below, the Remaining Qty shows the FIFO stock availability where as the Balance shows the total amount in stock for each record on that day.

image

Can you provide these transactions?

7

Hi @PhilBowen, we’ve noticed that no response has been received from you since the 28th of August. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!