Forecasting Dax help

[ashton99]

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

[SamWiseOwl · Accepted solution]

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 🙂