Forum Discussion

rajasekar_o's avatar
rajasekar_o
Helper V
2 years ago

inventory opening quantity closing quantity

hi team,
need help for  calculating inventory opening quantity and closing quantity

with out date beet ween date selection opening quantity and closing quantity correctly showing
if i select any beetween date its not show correct opening quantity and closing quantity
i select date from 02-04-2022 to 14-02-2024 the actual opening quantity 1000 and closing quantity 1000
but its show opening quantity 333 and closing quantity 333

how to solve this problem.
used dax :

opening quantity =
CALCULATE([rec qty]-[iss qty],
    FILTER(
        ALL('STOCK'[DOCDATE]),
        'STOCK'[DOCDATE] + 1 <= MAX('STOCK'[DOCDATE])
    )
)

 

closing qty=
CALCULATE([rec qty]-[iss qty],
    FILTER(
        ALL('STOCK'[DOCDATE]),
        STOCK[DOCDATE] <= MAX('STOCK'[DOCDATE])
    )
)
Need Help
Tips and Tricks

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    rajasekar_o , Prefer to use a date table

     

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

     

     

    Power BI Inventory On Hand: https://youtu.be/nKbJ9Cpb-Aw

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi rajasekar_o ,

    You can try this DAX:

    Opening Quantity:

    Opening Quantity = 
CALCULATE(
    [rec qty]-[iss qty], 
    FILTER(
        ALL('STOCK'[DOCDATE]), 
        'STOCK'[DOCDATE] < MIN('STOCK'[DOCDATE])
    )
)

    Closing Quantity:

    Closing Quantity = 
CALCULATE(
    [rec qty]-[iss qty], 
    FILTER(
        ALL('STOCK'[DOCDATE]), 
        'STOCK'[DOCDATE] <= MAX('STOCK'[DOCDATE])
    )
)

    Best Regards,

    Xianda Tang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.