Transaction Level Running Total

[Eric]

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 transaction level.  For example, in the table below note that the "Qty Before" on 10/3/17 shows the same number for each separate transaction. The number is correct for the end of the day but it is not correct for each separate transaction.  I need the "Qty Before" balance to iterate correctly so the ending balances will roll forward correctly on a transaction by transaction basis.

Here is the DAX code for my measures:

Quantity_Before = [Quantity_EndBal] - [Quantity_Reverse]

Quantity_EndBal =
CALCULATE (
   SUM ( TMPROD[PDINVENTRY] ),
   FILTER ( TMPROD, TMPROD[PDNUMBER] = MAX ( TMHIST[PDHSTPRDNO] ) )
   )  [Working Correctly]

Quantity_Reverse =

CALCULATE (

   SUM ( 'TMHIST'[Movement] ),

   ALLSELECTED ( ),

   ALL ( TMHIST[Date] )

   )

   - CALCULATE (

      SUM ( 'TMHIST'[Movement] ),

      ALLSELECTED ( ),

      FILTER (

         ALL ( TMHIST[Date] ),

         TMHIST[Date] < MAX ( TMHIST[Date] )

      )

)

[Working correctly on a daily basis but not on a transaction basis] 

This should be a simple formula but I can't get it to work after numerous different attempts. When I try substituting Ref No in place of Date in the FILTER(ALL( function Power BI runs out of memory and errors out.  For reference, the fact table has roughly 2 million records in it.

[Icey]

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]

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]

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]

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.

[Greg_Deckler]

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[Eric]

Greg, thanks for the response.  I haven’t tried the CALCULATETABLE function, I will give that some thought.  But, anytime we try to use SUMX(FILTER( we run out of memory even with a table that only has 2 million records.

Thanks,

Eric