Measure depends on its previous results - possible?

[hohlick]

Hi All,

I try to create a model for my logistics dept.  There is logic behind:

1. I have initial stock [IS] amount and daily sales plan [SP], also as Desired Stock Amount [DS]

2. I place an [Order] with size [DS] - ([IS] - [SP]) = [DS] - [IS] + [SP]

3. When order arrives, I calculate [Fact Stock]  = [IS] - [Fact Sales] + previous [Order].

To calculate next order, I have to use [Fact Stock] instead of [IS], but it depends on previous [Order] value.

Is it possible to calculate this model with measures? 

I can do it in Excel with referencing prev. cells, but in DAX I cannot find a solution.

[CheenuSing]

Hi Holick

Please check out 

http://community.powerbi.com/t5/Desktop/Recursive-calculation/td-p/97406

If this helps please give KUDOS.

Cheers

CheenuSing

[hohlick]

Hi @CheenuSing

thank you for your suggestion

Unfortunately, I can't implement this for my case. But you link is very helpful and I've found few ideas there for my searches

[hohlick]

There are initially these tables/columns:
Stock (date, sku, amount) - initially movements only, transforned to everyday basis
Sales (date, sku, amount)
Products (sku, name, price, etc.)
Plans (month, sku, amount)
Calendar (date, etc.)
Also there some constants like LeadTime, minimal stock in days, max stock in days...
All names from first message is a measures calculated from these tables

[v-shex-msft]

Hi @hohlick,

You can try to use below methods if it works on your side.

1. Write a measure to calculate the previous order value.
2. If your table contain index column, you can use calculated column to calculate the Order, then you can use index column to get the previous Order.

Regards,

Xiaoxin SHeng

[hohlick]

Hi @v-shex-msft

It is the first thing I tried to calculate. But I cannot implement it in my case...

It is like too much recursion for me

thanks anyway!

[OwenAuger · Accepted solution]

@hohlick

Hi Maxim,

Here is a dummy example of how this sort of calculation can be done.

PBIX dummy example

The logic is to first calculate Cumulative Sales/Orders. The differences between daily Cumulative Orders are then daily Orders, and Stock Level = Initial Stock - Cumulative Sales + Cumulative Orders.

I followed this sequence of calculation through a series of measures:

1. [Fact Sales] = Sum of Sales from Sales table
2. [Cumulative Sales] = Cumulative sum of Fact Sales
3. [Latest Desired Stock] = Desired stock from Desired Stock table
4. [Initial Stock (first day)] = constant value from Initial Stock table
5. [Cumulative Orders Placed] = [Latest Desired Stock] - ( [Initial Stock (first day)] - [Cumulative Sales])
6. [Cumulative Orders Placed 1 day ago] = [Cumulative Orders Placed] on previous day
7. [Cumulative Orders Placed 2 days ago] = [Cumulative Orders Placed 1 day ago] on previous day
8. [Orders Placed] = [Cumulative Orders Placed] - [Cumulative Orders Placed 1 day ago]
9. [Orders Received] = [Cumulative Orders Placed 1 day ago] - [Cumulative Orders Placed 2 days ago]
10. [Cumulative Change in Stock] = (-1)*[Cumulative Sales] + [Cumulative Orders Placed 1 day ago]
11. [Fact Stock Closing] = [Initial Stock (first day)] + [Cumulative Change in Stock]

Hopefully this can be adapted to your exact situation.

Notes:

• Orders are placed based on Desired Stock, but there is 1 day lead time so Desired Stock may not be attained (I think that was your situation)
• Steps 7-9 are not necessary for stock calculation, just for information purposes.
• Orders can be either positive or negative to get to Desired Stock (potentially unrealistic)
• If Orders are instead option-like (only made if stock is below Desired Stock), then you can change Cumulative Orders Placed to be the largest order that would have been required so far to get to Desired Stock, i.e. MAX( MAXX ( <All dates so far> , [Latest Desired Stock] - ( [Initial Stock (first day)] - [Cumulative Sales]), 0)
• Boundary conditions & grand totals probably need tidying up

Cheers,

Owen

[TorbenBoehrnsen]

Hi Owen, 
could you please elaborate on the note regarding only adding orders, if the stock level is below the desired Stock? 
I have tried to follow your example but my stock still keeps growing and growing. 
Thank you so much for your help. 
Torben

[TorbenBoehrnsen]

@OwenAuger , 
Thank you again so much for this example data. 
Could I please ask you for a little more help? 
I am trying to cap my stock at a certain value and only order more from production, if the stock is below the desired stock. 
Any ideas? 

Thanks, 
Torben

[OwenAuger]

Hi @TorbenBoehrnsen 

Thanks for the messages! I've been away from the forums for a bit and just catching up on this thread.

Let me refresh my memory and get back to you.

Regards,

Owen

[TorbenBoehrnsen]

Hi @OwenAuger ,
my appologies to keep asking. But did you have a chance to look at this problem again? 

Thank you so much, 

Torben 

[OwenAuger]

Hi @TorbenBoehrnsen 

I'm afraid things got away from me late last year, and I'm just now going through all the forum questions I have outstanding.

Are you still working on this, and if so it would be easier if you could share a PBIX illustrating the issue?

In the PBIX I created for the original question, I don't believe stock could increase without bound. In fact, if stock gets too high, my measures would create "negative" orders to bring it back down. There must be some difference in your model that allows stock to grow more than desired.

Regards,

Owen

[hohlick]

Hi @OwenAuger

+100500 kudos!

It looks like it solved my issues. At least in dummy it looks as desired. I hope I could make other changes to fit my model.

Thank you a lot again!!! I was totally confused with all complexity of my excisting model and cannot see it with a freshe eye then.

[OwenAuger]

Glad it was helpful!

Cheers,

Owen

[parry2k]

Did you looked at Earlier DAX function

https://msdn.microsoft.com/en-us/library/ee634551.aspx?f=255&MSPPError=-2147217396

Hope this will get what you are looking for? 

Thanks,

P

[hohlick]

HI! Thanks for suggestion, but all these values is a measures calculated in different tables, not columns. I don't understand how to use EARLIER there... in fact I can add some of these measures as a columns, but it will cause a huge size increment.
And... what data EARLIER should return?..