cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Frequent Visitor

## Stock on hand for future days - Dax logic

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

Super User

@PowerBIShan , Check if these can help

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors