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 value in the Stock on Hand column. I only have one value for the opening balance from then onwards; after every month's transaction, I need to know how much stock we have left on hand



Bales Received is a measure = SUM(Bales Received ) and Bales Sold as well, from multiple tables. Thanks.

Need Help

3 Replies

  • 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])))

  • heygau's avatar
    heygau
    Helper I
    2 years ago

    Hi amitchandak 

    I have to consider the given Opening balance (56,192 )from June 2023 on monthly Basis. Thank you