Forum Discussion

heygau's avatar
heygau
Helper I
2 years ago
Solved

DAX Help

  I want to calculate the stock on hand as follows: [(Opening Balance + Bales Received) - Bales Sold]. I need to consider the opening balance from June 2023 and continue with the resulting...
Need Help
amitchandak's avatar
amitchandak
Super User
2 years ago

heygau , seem like Inventory problem, here we will prefer to use Cumulative measure

 

Inventory / OnHand BOP=
CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),all('Date')) +
CALCULATE(SUM(Table[Ordered]),filter(all(date),date[date] <min(date[date]))) -
CALCULATE(SUM(Table[Sold]),filter(all(date),date[date] <min(date[date])))

 

 

Inventory / OnHand EOP=
CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),all('Date')) +
CALCULATE(SUM(Table[Ordered]),filter(all(date),date[date] <=max(date[date]))) -
CALCULATE(SUM(Table[Sold]),filter(all(date),date[date] <=max(date[date])))

 

 

or

 

Inventory / OnHand =
CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),filter(all(date),date[date] <min(date[date]))) +
CALCULATE(SUM(Table[Ordered]),filter(all(date),date[date] <=max(date[date]))) -
CALCULATE(SUM(Table[Sold]),filter(all(date),date[date] <=max(date[date])))