Forum Discussion

ashton99's avatar
ashton99
Regular Visitor
2 years ago
Solved

Forecasting Dax help

I've been trying to do this for a while and just cant figure it out. Im trying to "forecast" StartingBalance using palletsfromcontainers and palletsshipped. in excel it would be quite simple but i need it to update itself.

In excel it would be Starting balance + Pallets from containers - Pallets Shipped. The next row would then start with the final value from that and be Forecasted balance (from previous row) + Pallets from containers - Pallets Shipped.

 

This is what my dataset table looks like:

 

thanks

Tips and Tricks
  • SamWiseOwl's avatar
    SamWiseOwl
    2 years ago

    Hi ashton99 
    In this specific scenario could you do Starting value + all prior (Received - Dispatched).

    The DAX is:

    Forecast stock =
    var firstvalue = maxx(TOPN(1,'Test Table','Test Table'[Date],ASC),[Starting Balance])
    --Capture the first Starting balance in the data
    var currDate = [Date] --Capture the current date
    RETURN
    firstvalue + --Add the starting value
    SUMX( --Calculate the total of all Pallets received less pallets shipped
        FILTER('Test Table', [Date] <= currDate) --Only include rows on or before current row
        ,[Pallets From Containers] - [Pallets Shipped] --subtract shipped from returned
    )
    There is a function called OFFSET() and another call ONORBEFORE() which might do what you are thinking.
    There is also the possible answer using EARLIER, I wrote a blog about forecast calcuations here.
    Let me know if the above isn't doing what you would like.

    If it is please mark this as a solution for others to find 🙂

1 Reply

  • SamWiseOwl's avatar
    SamWiseOwl
    Super User
    2 years ago

    Hi ashton99 
    In this specific scenario could you do Starting value + all prior (Received - Dispatched).

    The DAX is:

    Forecast stock =
    var firstvalue = maxx(TOPN(1,'Test Table','Test Table'[Date],ASC),[Starting Balance])
    --Capture the first Starting balance in the data
    var currDate = [Date] --Capture the current date
    RETURN
    firstvalue + --Add the starting value
    SUMX( --Calculate the total of all Pallets received less pallets shipped
        FILTER('Test Table', [Date] <= currDate) --Only include rows on or before current row
        ,[Pallets From Containers] - [Pallets Shipped] --subtract shipped from returned
    )
    There is a function called OFFSET() and another call ONORBEFORE() which might do what you are thinking.
    There is also the possible answer using EARLIER, I wrote a blog about forecast calcuations here.
    Let me know if the above isn't doing what you would like.

    If it is please mark this as a solution for others to find 🙂