cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

## Inventory opening ,closing quantity

hi team,
how to calculating inventory opening quantity and closing quantity.

with out beetween 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 calculate opening quantity closing quantity

1 ACCEPTED SOLUTION
Helper III
@guruvammal
Closing Qty =
CALCULATE(CALCULATE(SUM(STOCKVALUE[Qty]),FILTER(STOCKVALUE,STOCKVALUE[PLUSORMINUS]= "P")) ,WINDOW(0,ABS,0,REL, ALL(STOCKVALUE[ITEMID], STOCKVALUE[DOCDATE]), ORDERBY([Date]))) - CALCULATE(SUM(STOCKVALUE[Qty]), WINDOW(0,ABS,0,REL, ALL(STOCKVALUE[ITEMID], STOCKVALUE[DOCDATE]), ORDERBY([Date])), FILTER(STOCKVALUE,STOCKVALUE[PLUSORMINUS]= "M"))
4 REPLIES 4
Helper III
@guruvammal
Closing Qty =
CALCULATE(CALCULATE(SUM(STOCKVALUE[Qty]),FILTER(STOCKVALUE,STOCKVALUE[PLUSORMINUS]= "P")) ,WINDOW(0,ABS,0,REL, ALL(STOCKVALUE[ITEMID], STOCKVALUE[DOCDATE]), ORDERBY([Date]))) - CALCULATE(SUM(STOCKVALUE[Qty]), WINDOW(0,ABS,0,REL, ALL(STOCKVALUE[ITEMID], STOCKVALUE[DOCDATE]), ORDERBY([Date])), FILTER(STOCKVALUE,STOCKVALUE[PLUSORMINUS]= "M"))
Super User

@guruvammal , with help from date table you can try measuring like

Onhand Opening= 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 Closing = 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

Frequent Visitor

@amitchandak  I have already tried this DAX, but it's not working. Is there an alternate solution.

Frequent Visitor

@amitchandak  I have already tried this DAX, but it's not working. Is there an alternate solution.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors