Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Many thanks for your help!
Kind Regards, Marieke
@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)
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |