Forum Discussion
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?
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)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
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
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)Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
