Inventory Ledger

[guruvammal]

Hi team I need help

In qlik can calculate closing qty but not able to calculate in power bi 

Qilk Expression

Closing Qty = Rangesum(above(total Sum ({<Trans_Date={"<=$(=MAX(Stock_Date))"}>} IF(PLUSORMINUS='p', QTY, -QTY)),0, RowNo()))

Power BI Measure

CLosing = 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"))

this measure not calculate correct closing qty

[Anonymous]

Hi @guruvammal , 

Create a Calculated Column for Adjusted Quantity: 

Adjusted Qty = IF(STOCKVALUE[PLUSORMINUS] = "P", STOCKVALUE[Qty], 0-STOCKVALUE[Qty])

 Create a measure:

Closing Qty = 
CALCULATE(
    SUM(STOCKVALUE[Adjusted Qty]),
    FILTER(
        ALL(STOCKVALUE),
        STOCKVALUE[DOCDATE] <= MAX(STOCKVALUE[DOCDATE])
    )
)

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[guruvammal]

@Anonymous 

Is not give the correct closing qty 

Excepted closing qty /Output

[Anonymous]

Hi @guruvammal ,

Is it a total value error? If so, please create another measure.

result =
var _b = SUMMARIZE(STOCKVALUE,STOCKVALUE[DATE],"aaa",STOCKVALUE[Closing Qty])
return
IF(HASONEVALUE(STOCKVALUE[date]),[Closing Qty],SUMX(_b,[aaa]))

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If the whole formula is not correct, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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