# Turn static time measure for stock level into dynamic time measure

Hi All,

at this moment I’m working on a project to calculate a stock aging overview.

The difficult thing in this case is that we don’t have a table with dates of arrival for the goods to arrive, so we can’t use that arrival date to calculate something like Today -/- Arrival date is aging days

But based on the daily stock levels (Stockmovement table provides us a daily snapshot table that gives the stock per product per day), and the Sales numbers, I have managed to calculate the purchases per day/period.

Then I have made several measures to calculate Purchases per Quarter.

I have used a lot of calculated columns to determine purchases by Quarter for each product, and from that I have more calculated columns to determine aging periods for every period. I know calculated columns should be avoided, but this was the only way for me to get this set up.

However now, this is all based on fixed dates from the Calender table. So it gives us the situation for today, and current Quearter is now Q2 in 2021, but we want it to be possible to look at any point in time and then show the situation at that choosen moment.

I have tried to work out something simular as in this video as that is what I’m looking for (only for stock instead of invoices) , but as I don’t have a table with all goods arrival dates (to use it like the invoice date in this example), I find it quit difficult to set this up correctly.

I have attached a small dummy version of the report.

It would be great if somebody can point me into the right direction to get this sorted…

Kind regards, Marieke

Aging Report Dummy.pbix (9.0 MB)

Hi Marieke,

Please receive hereby version 4, simplified to the better, including a dynamic working quarterly stock aging (see also print screen below, the Aging categories > 1 year make the report slow) .
Aging Report Dummy v4.pbix (4.8 MB)

I have a possible workout, which gives the inventory and the aging, calculated on a variable daily base, with no calculated columns, all based upon measures.

• Inventory can be reported per quarter or per day, defined as cum. purchases (incl start inventory due to the way it is calculated) -/- cum. sales.
• Aging can be reported on a daily basis, in quarterly reporting and day reporting

Concept :

• Workout based upon quantity (qty), value not taken into account.
• Calculation of purchases per day, [ based upon daily stock mutation plus daily sales ] and cum. purchases.
• Calculation of sales per day, [ based upon daily sales information ] + cum. sales
• For the aging reporting the First In First Out (FIFO) approach is used. The more recent period is selected, the more early stock is sold out.

Conditions:

• The stock movement - and sales tables should have the same starting date, in order not to over- or understate the available stock per period, given the way the stock is calculated (purchases - sales).
• The initial stock (opening balance) should be included in the purchases (this happens by default with the calculation of Oty purchases stockmvt[1] - stockmvt[0] + sales at the first line of the table Stockmvmt, And therefore the time slicer should always start with the initial stock movements, only be varied with the end date.

Technical description on the work-out:
(simular calculations are used as from your Aging Report Dummy)

Data model

Two fact- tables “StockMHT”, the daily stock movement table and “SalesT” and two Lookup tables KalenderT and ProductT. Might be an idea to join SKU with product-ID in one Lookup table.

Measure Groups Explained:

1 Base Qty Measures :

Measures are straight forward, speaks for itself.
Purchase Qty is derived from daily stock movement + sales, with adjusted formula:

``````Qty Purchase = //  // formula to determine daily base purchases defined as  / based upon daily stockmovement plus the daily sales
var PrevDayInv = CALCULATE( SUM( StockMHT[qty]), DATEADD( Kalender[Date], -1, DAY))
var CurrDayInv = CALCULATE( SUM( StockMHT[qty]), DATEADD( Kalender[Date], 0, DAY))
return
[Qty Sales] + CurrDayInv - PrevDayInv
``````

Dynamic inventory reporting become possible with this measure group, when inventory is defined as cum. purchases (includes beginning inventory) - cum. sales. Therefore the simple measure Inventory is used.

Next measure groups are Aging, quarterly and daily, whereby quarterly is mostly requested.

An example of the quarterly aging formulas:

AgingQ till90d =
Var Till90D = CALCULATE( [Purch - Sales], DATEADD( Kalender[Date], -1, QUARTER))
Var Limitering1 = IF(and([Inventory] > 0, Till90D > 0), Till90D, 0 )
Var Limitering2 = IF(Limitering1 > [Inventory] - [AgingQ Current] , [Inventory] - [AgingQ Current] , Limitering1)
return
IF( OR( [Cum Sales Qty] > 0, [Cum Purchases Qty] >0), Limitering2,BLANK())

Restrictions:
Slow, when using aging between 1-2 years, and category “> 2 years” , still to be added.

Short testing shows that the quarterly inventory aging report calculates correctly, solid testing is advisable.
For example: the stock as per 1 February 2021 must be at least one year old, as three quarters of net negative stock movements (“Purch - Sales”) are preceeding.
Please note that however the quarter might not be finished (1 February 2021), for aging calculations it is seen as a full quarter (quasi 31 March 2021)

APPENDIX
The principle Aging, as explained by EDNA in the video for Account Receivable, does not work in this case from inventory aging, as the formula checks to an invoice values row for row in the AR invoice table, which is in this example not available, all is done by measure.

3 Likes

Hi @marieke, did the response provided by @deltaselect help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

Hi @marieke, we’ve noticed that no response has been received from you since the 2nd of June. 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.

Hi @deltaselect ,

thank you for your help! This looks great and seems to be exactly what I want, so that is very great!

Kind regards, Marieke

1 Like