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.
Many thanks for your help!
Kind Regards, Marieke