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))"}>} 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

Need Help
Tips and Tricks
Tutorial Requests

3 Replies

  • 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.