Transaction Level Running Total

Question

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.

greg_deckler · Answer

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-TITHW/m-p/434008

icey · Answer

Hi&nbsp;Eric&nbsp;,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.For your computer, close other non-essential programs.For your data model, you can refer to this article to optimize your model.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&nbsp;Best Regards,Icey Zhang&nbsp;If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

eric · Answer

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

eric · Answer

Thanks for the reply, Icey.&nbsp; Let me be a bit more specific about the error I'm trying to fix.&nbsp; See the table below for more info:&nbsp;Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Product&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Ref No&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Qty Before&nbsp; &nbsp; &nbsp; &nbsp; Movement&nbsp; &nbsp; &nbsp; &nbsp;Qty After10/02/17&nbsp; &nbsp; &nbsp; &nbsp;03885&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7065503&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1455&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; +4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;145910/03/17&nbsp; &nbsp; &nbsp; &nbsp;03885&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7071406&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1459&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1454&nbsp;10/03/17&nbsp; &nbsp; &nbsp; &nbsp;03885&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7071433&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1459&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1454&nbsp;10/03/17&nbsp; &nbsp; &nbsp; &nbsp;03885&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7071908&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1459&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1454&nbsp;&nbsp;&nbsp;My main problem is with the calculation of the Qty Before (in red above).&nbsp; Here is the desired outcome:&nbsp;Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Product&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Ref No&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Qty Before&nbsp; &nbsp; &nbsp; &nbsp; Movement&nbsp; &nbsp; &nbsp; &nbsp;Qty After10/02/17&nbsp; &nbsp; &nbsp; &nbsp;03885&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7065503&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1455&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; +4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;145910/03/17&nbsp; &nbsp; &nbsp; &nbsp;03885&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7071406&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1459&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1457&nbsp;10/03/17&nbsp; &nbsp; &nbsp; &nbsp;03885&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7071433&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1457&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1455&nbsp;10/03/17&nbsp; &nbsp; &nbsp; &nbsp;03885&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7071908&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1455&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1454&nbsp;&nbsp;&nbsp;And here is he formula for the Qty Before measure:&nbsp;&nbsp;&nbsp;Quantity_Reverse&nbsp;=CALCULATE (&nbsp; &nbsp;SUM ( 'TMHIST'[Movement] ),&nbsp; &nbsp;ALLSELECTED ( ),&nbsp; &nbsp;ALL ( TMHIST[Date] )&nbsp; &nbsp;)&nbsp; &nbsp;- CALCULATE (&nbsp; &nbsp; &nbsp; SUM ( 'TMHIST'[Movement] ),&nbsp; &nbsp; &nbsp; ALLSELECTED ( ),&nbsp; &nbsp; &nbsp; FILTER (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ALL ( TMHIST[Date] ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;TMHIST[Date] &lt; MAX ( TMHIST[Date] )&nbsp; &nbsp; &nbsp; ))&nbsp;It should be a simple fix but I just can see what needs to change in my formula to make the iteration work properly.&nbsp;Any help will be appreciated.&nbsp;Thanks,&nbsp;Eric&nbsp;

icey · Answer

Hi&nbsp;Eric&nbsp;,I reproduce your question, and I think the results are correct.&nbsp;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] &lt; MAX ( TMHIST[Ref No] ) )
    )Best Regards,Icey&nbsp;If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.&nbsp;

Forum Discussion

Transaction Level Running Total

6 Replies

Related content

Running Maximum

Running sum

Most Recent Transaction

Running Totals

Pipeline not running