Forum Discussion
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 ne...
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 datavar currDate = [Date] --Capture the current dateRETURNfirstvalue + --Add the starting valueSUMX( --Calculate the total of all Pallets received less pallets shippedFILTER('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 🙂
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.
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 🙂