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