cancel
Showing results for
Did you mean: Helper III

## Running total based off column in second table

Hello,

I am trying to produce a running total based off of 2 tables. Example below:  They are connected by a relationship in the 'GL Account' columns on both tables. I am trying to produce a running total using the 'Date' (table 1) from the 'Amount' column in table 1 IF the GL account Description (on table 2) is UOO, AEU or AEP.

Any Ideas?

1 ACCEPTED SOLUTION Microsoft

Hi @nmyre ,

Try this measure :

``````RunningTotal =
var RunningTotal_all = CALCULATE(
SUM( Sheet6[amount] ),
FILTER(  ALL(Sheet6) ,
SUMX( FILTER( Sheet6, EARLIER( Sheet6[date] ) <= Sheet6[date]&&EARLIER(Sheet6[GL Account])=Sheet6[GL Account]), Sheet6[amount] )
)
)
return IF(MAX(Sheet7[GL Description code])in {"AEU","UOO","AEP"},RunningTotal_all)``````

Sample .pbix

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

2 REPLIES 2 Microsoft

Hi @nmyre ,

Try this measure :

``````RunningTotal =
var RunningTotal_all = CALCULATE(
SUM( Sheet6[amount] ),
FILTER(  ALL(Sheet6) ,
SUMX( FILTER( Sheet6, EARLIER( Sheet6[date] ) <= Sheet6[date]&&EARLIER(Sheet6[GL Account])=Sheet6[GL Account]), Sheet6[amount] )
)
)
return IF(MAX(Sheet7[GL Description code])in {"AEU","UOO","AEP"},RunningTotal_all)``````

Sample .pbix

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

To build a running total, you need a numeric column in your chart of accounts that can be used for ordering the running total. I suggest you look at my article and video here  https://exceleratorbi.com.au/build-a-pl-with-power-bi/

it covered everything needed to build a P&L

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.   