Stock on hand for future days - Dax logic

PowerBIShan — Mon, 05 Dec 2022 09:15:58 GMT

Hi all
I am trying to achieve a logic using DAX.

Logic
Till current date I need to show the stock that is actually available in the Inventory. For future days, it should show based on the logic (Previousday stock -Last 3months sales average) as projection

Measures
1.last 3 months sales avg =

var a =calculate(sum('Sales[Quantitysold]),FILTER(all('Calendar'[Year]),'Calendar'[Year]=max('Calendar'[Year])),FILTER(all('Calendar'),'Calendar'[Month No]>max('Calendar'[Month No])-3 &&'Calendar'[Month No]<=max('Calendar'[Month No])))

var b = CALCULATE(DISTINCTCOUNT('Calendar'[Date]),FILTER(all('Calendar'[Year]),'Calendar'[Year]=max('Calendar'[Year])),FILTER(all('Calendar'),'Calendar'[Month No]>max('Calendar'[Month No])-3 &&'Calendar'[Month No]<=max('Calendar'[Month No])),'Calendar'[Week day]<>"Sat" &&'Calendar'[Week day]<>"Sun")

return

a/b

2.Stock on hand =
var previousdaystock =

CALCULATE(sum(Inventory[Stock On Hand]),FILTER(all(Inventory[futuredate]),Inventory[futuredate]=max(Inventory[futuredate])-1))

if(SELECTEDVALUE(Inventory[Stockdate])<=max(Inventory[Stockdate]),sum('Inventory'[Stockinhand]),previousdaystock-last3monthssalesavg

For example ,
Max stockdate (in data )=12/5/2022
last 3 month sales avg
For Dec 2022,it is 40 ( Last 3 month sales avg differs from month to month )
Projection for product A
eg. 12/6/2022 = previousday stock -daily sales => 990 - 40 =950
12/7/2022 = 950-40 = 910
12/8/2022 =910-40 = 870
12/9/2022 =870-40 = 830 and it goes on....

	actual	actual	Proj	Proj	Proj	Proj	Proj	Proj	Proj
Product	12/4/2022	12/5/2022	12/6/2022	12/7/2022	12/8/2022	12/9/2922	12/10/2022	12/11/2022	12/12/2022
A	1000	990	950	910	870	830	790	750	710
B	800	750	710	670	630	590	550	510	470
C	400	350	310	270	230	190	150	110	70

Please let me know if you have any idea on how to achieve the above result using dax.
Need help in achieving the above requirement

topic Re: Stock on hand for future days - Dax logic in DAX Commands and Tips

Stock on hand for future days - Dax logic

Re: Stock on hand for future days - Dax logic