Calculate Stock level for past and future months, based on current stock level.

[MarieD]

Hi all,

I need help with a DAX calculation to get the stock levels for future and past months, based on current stock level and Sales/Purchase figures.

Sales and purchases figures for future months are predictions, for past months these are the real figures.

In the attached Excel file I have an example of how it should look like. Based on the Sales and Purchase figures, I have calculated the Stock change for each month.

But I only know the current Stock Level. So for future weeks, it should give me the Current Stock level + the stock change for every upcoming month YTD. And for the past months, it should count back from the current stock level with the stock changes in future months.

I have tried several things with YTD and DATESBETWEEN, but wihout succes unfortunatelly, so I hope somebody here can help me out.

Attached a dummy PBIX file and an Excel mockup with the calculations how I want them to be:

https://we.tl/t-nxsEaavdQN

Many thanks for your help!

Kind Regards, Marieke

[amitchandak]

@MarieD , Assuming you do not stock for Sep-2021, And you got it with help from YTD. Then if you need stock of Oct-2021 and you need sep-2021, You cannot get that. You need to create a formula in such a manner that you can get Oct, Nov all from august

ProductX can help in this

Sep = 1+X

Oct = (1+X)*(1+X)

[MarieD]

Hi @amitchandak 

Thanks for your reply. I understand I need to have a calculation for this, but I dont know how to do this.

It should be something like this logic:
If periode < 01-09 it should give me STOCK -/- Stock change in previous months
If periode > 01-09 it should give me STOCK + Stock change in upcoming months (and off course for upcoming months these are forecasting numbers)