Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

STOCK FLOW

Hello everyone, how are you? I develop a project whose objective is to calculate the stock of certain customers using 3 tables. fPurchases, fSales and fStock I carried out some measures to...
Need Help
  • amitchandak's avatar
    amitchandak
    2 years ago

    Anonymous , You have try measures like

     

    Onhand BOP= CALCULATE(SUM(Table[Ordered]),filter(all(date),date[date] <min(date[date]))) -
    CALCULATE(SUM(Table[Sold]),filter(all(date),date[date] <min(date[date])))


    onhand EOP= CALCULATE(SUM(Table[Ordered]),filter(all(date),date[date] <=Max(date[date]))) -
    CALCULATE(SUM(Table[Sold]),filter(all(date),date[date] <= Max(date[date])))

     

    If need add purcahse and sales

     

    example

     

    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] <min(date[date]))) -
    CALCULATE(SUM(Table[Sold]),filter(all(date),date[date] <min(date[date])))


    Inventory / OnHand =
    CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),filter(all(date),date[date] <=max(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])))

amitchandak's avatar
amitchandak
Super User
2 years ago

Anonymous , You have try measures like

 

Onhand BOP= CALCULATE(SUM(Table[Ordered]),filter(all(date),date[date] <min(date[date]))) -
CALCULATE(SUM(Table[Sold]),filter(all(date),date[date] <min(date[date])))


onhand EOP= CALCULATE(SUM(Table[Ordered]),filter(all(date),date[date] <=Max(date[date]))) -
CALCULATE(SUM(Table[Sold]),filter(all(date),date[date] <= Max(date[date])))

 

If need add purcahse and sales

 

example

 

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] <min(date[date]))) -
CALCULATE(SUM(Table[Sold]),filter(all(date),date[date] <min(date[date])))


Inventory / OnHand =
CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),filter(all(date),date[date] <=max(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])))

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hello friend

This way the same problem occurs. After a new load of data, with new months, the measure no longer considers the stock adjusted for the difference. I believe I need to create something like a loop.

But thanks