Home Forums Power Pivot Forecast inventory based on average sold per day

This topic contains 2 replies, has 2 voices, and was last updated by  pvanerk 1 month, 3 weeks ago.

Viewing 3 posts - 1 through 3 (of 3 total)
  • Author
    Posts
  • #10601

    pvanerk
    Participant
    • Started: 1
    • Replies: 1
    • Total: 2

    Hi,

     

    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:
    see attachment

    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.
    #10603

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2545
    • Total: 2552

    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.

    #10669

    pvanerk
    Participant
    • Started: 1
    • Replies: 1
    • Total: 2

    Thanks!

    I have the book of Rob Collie. I will have a look at the specified chapters.

    Paul

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic.