cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jmcauley88
Frequent Visitor

Rolling stock position

Hi,

 

Grateful for any help.

 

I have a data source which looks like below: Many customers, markets and products.

 

ForecastShipmentDateCustomerMarketSKUForecastTypeForecastName9Lc
01/01/2021AnyCustUK11111ShipmentForecastX2
01/01/2021AnyCustUK11111DepletionForecastX1
01/02/2021AnyCustUK11111ShipmentForecastX2
01/02/2021AnyCustUK11111DepletionForecastX1
01/03/2021AnyCustUK11111ShipmentForecastX2
01/03/2021AnyCustUK11111DepletionForecastX1
01/04/2021AnyCustUK11111ShipmentForecastX2
01/04/2021AnyCustUK11111DepletionForecastX1
01/04/2021AnyCustUK11111StockForecastX10
01/05/2021AnyCustUK11111ShipmentForecastX2
01/05/2021AnyCustUK11111DepletionForecastX1
01/06/2021AnyCustUK11111ShipmentForecastX2
01/06/2021AnyCustUK11111DepletionForecastX1

 

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.

10 REPLIES 10
daxer-almighty
Solution Sage
Solution Sage

@jmcauley88 

 

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.

Super thank you

daxer
Solution Sage
Solution Sage

@jmcauley88 

 

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?

jmcauley88
Frequent Visitor

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?

 

Thanks

@jmcauley88 

 

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.

Hi @jmcauley88 

 

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 🙂

jmcauley88
Frequent Visitor

Thanks again Daxer.

 

Apologies, I've not disclosed... there is a star schema around this... 

 

jmcauley88_0-1619436798740.png

 

@daxer I've been programmed to avoid adding calculated columns 😂

 

Can you talk me through the proposal to add the values as a column?

@jmcauley88 

 

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.

Helpful resources

Announcements
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors