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) .
I hope this answers your request.
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.
