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.