Grateful for any help.
I have a data source which looks like below: Many customers, markets and products.
For each forecast a customer will provide a stock position at the close of the previous month (this then becomes Aprils opening stock as shown above) and what I'm trying to do is create a rolling stock position.
So Stock + Shipments - Depletions = Closing stock.
I have created a number of different measures but can't get the knack of this as May, June etc. don't have an opening stock to work with.
Effectively what I'm trying to create is:
Ignore months before stock date (April in this case)
For April: Take the Stock position and add shipments minus depletions to create a closing stock
For May: Take Aprils "closing stock" and add May's shipments minus May's depletions to create a May closing stock
For June: Take May's "closing stock" and add June's shipments minus June's depletions to create a June closing stock
July, August, Sept...Dec
As there is only a stock position for April I'm tying myself up in knots a little and would appreciate any help with the correct formula.
Aha... OK. I'll think about it. This doesn't look to be too hard but I have to play around with this a bit. I'll post a link to a file with some examples later.
That's no too hard but... what about months where there's no stock, only shipments and depletions? You should give a rule for any date, not only for those where you've got a stock entry in the table. A measure should work correctly in all circumstances. So, say, you want to see the closing stock in January. What is the figure going to be? Again, say, that you've got two consecutive months where there is an entry for the stock. How would you then calculate the closing stock for the first and the second month?
Hi @daxer thanks for the response.
It's the months with no stock that are causing the issue and that is in effect what I'm trying to create.
If I receive a forecast from the distributor in say April, I'll receive only an opening stock for April but I'll receive shipments and depletions for all months.
The logic is relatively straightforward but the formulas causing a mental block.
The logic is...
Ignore everything before Apil
Take Aprils opening stock and add shipments minus depletions
This creates a "virtual opening stock" for May.
Take Mays "virtual opening stock" and add shipments minus depletions...
repeat for all outlook months.
Effectively, what I'm trying to create is a "virtual opening stock" for all months starting from April where I have the actual opening stock.
Any thoughts on the formula?
This code I give you is ugly and terrible from the maintenance point of view because this should really be carried out in Power Query. On top of that, you have not provided a representative sample of your data since you only have one SKU, one Market, one Customer and one Forecast Name in the table.
Closing Stock = var vShipmentEndDate = MAX( T[Forecast Shipment Date] ) var vSKUsWithFirstStock = FILTER( ADDCOLUMNS( SUMMARIZE( T, T[SKU], T[Customer], T[Forecast Name], T[Market] ), "@FirstStockDate", CALCULATE( MIN( T[Forecast Shipment Date] ), T[Forecast Type] = "Stock", T[Forecast Shipment Date] <= vShipmentEndDate, ALLEXCEPT( T, T[SKU], T[Customer], T[Forecast Name], T[Market] ) ) ), NOT ISBLANK( [@FirstStockDate] ) ) var vResult = SUMX( vSKUsWithFirstStock, var vStartDate = [@FirstStockDate] return CALCULATE( SUMX( T, T[9Lc] * ( ( T[Forecast Type] = "Stock" ) + ( T[Forecast Type] = "Shipment" ) - ( T[Forecast Type] = "Depletion" ) ) ), T[Forecast Shipment Date] >= vStartDate, T[Forecast Shipment Date] <= vShipmentEndDate, ALLEXCEPT( T, T[SKU], T[Customer], T[Forecast Name], T[Market] ) ) ) return vResult
Thanks @daxer-almighty I'm on leave this week so haven't tried the above but will test it shortly.
Would your recommendation be to pivot the ForecastType Column?
I've attached a more representative sample below. I control the dataset so can do whatever needs to be done to get the outcome. Open to any suggestions to get the right outcome.
No, I wouldn't pivot it. But I would create a proper star-schema model out of this one table to avoid the many pitfalls that await you with this design. Please check this out: Understand star schema and the importance for Power BI - Power BI | Microsoft Docs. Note at that as well that it's a well-known fact that improper one-table models (and all one-table models ARE improper) suffer from an issue that can't be overcome without building a good model: depending on the distribution of values in columns, DAX - even though correctly(!) written - may return wrong results (of which you won't even be aware, there's an article about it at www.sqlbi.com). This is a direct consequence of the so-called "autoexist optimization."
Additionally, I wouldn't calculate the closing/opening stock in DAX but in Power Query, adding the values as a column in the above (fact) table.
But, of course, it's up to you how you're going to make your life harder 🙂
@daxer I've been programmed to avoid adding calculated columns 😂
Can you talk me through the proposal to add the values as a column?
Who's programmed you? It's true that calculated columns should be your very, very, VERY last resort but it's all about calculated columns in DAX.
But... I'm not talking about this animal. Talking about massaging data in Power Query. A completely different thing. When I get some time today, I'll try to write some M code to show you how I do what I preach.
Click here to read more about the October 2021 Release!
Explore the latest tools,training sessions,technical expertise, networking and more.
Mark your calendars and join us for our next Power BI Dev Camp!