Calculating month end balance of stock/inventory

[nthomson]

Hello!

I have a table that shows the qty and value of stock by item and by warehouse location. A new line is added to the table each time an item in a location increases of decreases in qty, so the table will always contain the latest and the historic values for each item.

My goal is to create a report that shows the most recent effective value (qty and value) for previous months. i.e what the effective value was at the end of March, April, May, etc.

The data is all in the table, but I’m struggling due to how it is presented. I have attached the pbix file with sample stock  data and a date table.

PBIX is Here

Explanation of other fields:

History Rank: this is captured per ITEM_ID per WAREHOUSE_ID. 1 is the most recent value, 2 the second most recent, etc.

Valid From/Date: this is the date the qty’available (and correspondingly the value) changed in each case and a new value was created.

Challenge

I have tried to use groupings in Power Query to distil the data to the most recent value (lowest History rank) per month, per Warehouse, per item.

However, I’m not sure how to account for an item where the value does not change every month. E.g Item 2, the last changes were in February, where the closing balance was 20pcs (history rank 1 in WAREHOUSE_ID 5). This balance of 20 should be carried forward in calculations, and show in the totals for March, April etc since there has been no change since.

Expected result:

Using the pbix data, my desired report would show the following:

	Feb-24	Mar-24	Apr-24	May-24	Jun-24
ITEM_ID	Qty. Available	Qty. Available	Qty. Available	Qty. Available	Qty. Available
1	199077	320635	546881	533409	544125
2	24136	24136	24136	24136	24136
Total	223213	344771	571017	557545	568261

I have also done some reading about the concept of ‘forward filling’ when looking for an answer. Could this be a potential solution?

Help much appreciated, thanks in advance.

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[NaveenGandhi · Accepted solution]

Hi @nthomson 

Forward filling might be better solution rather than trying to achieve this with DAX.

Attached PBIX has a PQ solution for the table structure you have provided. Let me know if this will work for you.

You can very well optimise it if you can.
 If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

[nthomson]

Many thanks @NaveenGandhi , this is very helpful. This does get the result I want in the sample file, although my actual table has about 3m lines so even after ~15mins it had not finished calculating when I applied the code... so unless there is a way to optimise it significantly I think I will need to find a different solution - any ideas?