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
