Forum Discussion

guruvammal's avatar
guruvammal
Frequent Visitor
2 years ago

Inventory Ledger

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))"}>} ...
Need Help
Tips and Tricks
Tutorial Requests
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
guruvammal
Frequent Visitor
2 years ago

Anonymous 

Is not give the correct closing qty 

Excepted closing qty /Output

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.