Skip to main content
cancel
Showing results for 
Search instead 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

Reply
PowerBIShan
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....

 

actualactualProjProjProjProjProjProjProj

Product

12/4/202212/5/202212/6/202212/7/202212/8/202212/9/292212/10/202212/11/202212/12/2022

A

1000990950910870830790750710
B800750710670630590550510470
C40035031027023019015011070


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

1 REPLY 1

Helpful resources

Announcements
Europe Fabric Conference

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.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors