Tagged: forecast dates
September 14, 2018 at 11:05 am #10601
- Started: 1
- Replies: 0
- Total: 1
I could use some help on this one 🙂
I want to predict my stock levels for the next 4 weeks. Let’s say I have 1 article. The average sold per day (based on the last 90 days) is 2.
At day 1 I have a stock level of 10.
In the columns I have the dates for the next 4 weeks.
I want to show, for every date in the next 4 weeks what the stock level will be. So that would be the stock level in the beginning – the average sold per day * the difference in days between the starting date (for which I know the stock level) and the date in the column.
So it would look like this:
How can I create this measure? I can’t manage to set a start date for my measure to calculate from, because the context is always set from the column date.
Next step would be to add the amounts of a purchase orders that are deliverd on a certain date, but that’s step 2.
Can somebody help me out?
Attachments:You must be logged in to view attached files.September 14, 2018 at 3:04 pm #10603
- Started: 7
- Replies: 2461
- Total: 2468
If you are a beginner with Power Pivot, this one would probably be easily done in regular Excel.
You have already discovered that when you have no data, that it is an uphill (but not impossible) task in overcoming filter context. This, combined with the fact that cells in a Power Pivot table are calculated without reference to any other cell, will make your formulas complex.
That said, you could obtain a start date by using a slicer with a disconnected table of dates. You can read about disconnected tables and slicers in Rob Collie’s book, “Power Pivot and Power BI”, chapters 12 and 13.
You must be logged in to reply to this topic.