Forum Discussion

Eric's avatar
Eric
Advocate I
7 years ago

Transaction Level Running Total

I am trying to calculate running balances at a transaction level for inventory.  The measures are working on a daily level but I cannot get the "Qty Before" balances to calculate correctly at a trans...
Need Help
Icey's avatar
Icey
Community Support
7 years ago

Hi Eric ,

I reproduced your question, but did not get any error. Is the result below what you want?

For your problem - ‘Power BI runs out of memory and errors out’, you can do something like below to improve performance.

  1. For your computer, close other non-essential programs.
  2. For your data model, you can refer to this article to optimize your model.
  3. For DAX, there are more different records in [Ref No] and ‘Filter’ need more time and memory to complete. You can use DAX studio to measure DAX performance and refer to this article to optimizing DAX

 

Best Regards,

Icey Zhang

 

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

  • Eric's avatar
    Eric
    Advocate I
    7 years ago

    Thanks for the reply, Icey.  Let me be a bit more specific about the error I'm trying to fix.  See the table below for more info:

     

    Date            Product           Ref No          Qty Before        Movement       Qty After

    10/02/17       03885             7065503           1455                    +4                 1459

    10/03/17       03885             7071406           1459                     -2                 1454 

    10/03/17       03885             7071433           1459                     -2                 1454 

    10/03/17       03885             7071908           1459                     -1                 1454  

     

    My main problem is with the calculation of the Qty Before (in red above).  Here is the desired outcome:

     

    Date            Product           Ref No          Qty Before        Movement       Qty After

    10/02/17       03885             7065503           1455                    +4                 1459

    10/03/17       03885             7071406           1459                     -2                 1457 

    10/03/17       03885             7071433           1457                     -2                 1455 

    10/03/17       03885             7071908           1455                     -1                 1454  

     

    And here is he formula for the Qty Before measure:  

     

    Quantity_Reverse =
    CALCULATE (
       SUM ( 'TMHIST'[Movement] ),
       ALLSELECTED ( ),
       ALL ( TMHIST[Date] )
       )
       - CALCULATE (
          SUM ( 'TMHIST'[Movement] ),
          ALLSELECTED ( ),
          FILTER (
             ALL ( TMHIST[Date] ),
             TMHIST[Date] < MAX ( TMHIST[Date] )
          )
    )
     
    It should be a simple fix but I just can see what needs to change in my formula to make the iteration work properly.
     
    Any help will be appreciated.
     
    Thanks,
     
    Eric
     
    • Icey's avatar
      Icey
      Community Support
      7 years ago

      Hi Eric ,

      I reproduce your question, and I think the results are correct. Can you share your PBIX to me if possible? Or a similar one. Then I can help you better.

      Quantity_Reverse 2 =
CALCULATE ( SUM ( 'TMHIST'[Movement] ), ALLSELECTED (), ALL ( TMHIST[Ref No] ) )
    - CALCULATE (
        SUM ( 'TMHIST'[Movement] ),
        ALLSELECTED (),
        FILTER ( ALL ( TMHIST[Ref No] ), TMHIST[Ref No] < MAX ( TMHIST[Ref No] ) )
    )

      Best Regards,

      Icey

       

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

       

      • Eric's avatar
        Eric
        Advocate I
        7 years ago

        Thanks for you help.  In the end, I was able to solve the problem by coming at it from a different direction and didn't have to use this formula after all.