cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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 - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors