Running total with Beginning Balance

Hello all,

I’m pretty new here and still just learning PowerBI, so please bear with me.

I need to add a column for a running total for a bank account that has a beginning balance.

In the example I’m providing, the selected account 1121 has a beginning balance of -150.00. The next transaction is a debit of 5.00, so the true balance would be -155.00. I have written a measure that will do a cumulative total of the credits and debits, “CumCred-Deb” but it does not take the beginning balance into account. I need to write a measure that will just return the beginning balance of the selected account so I can add it to “CumCred-Deb” but so far have been unsuccessful, as it always adds in all the lines for all the accounts, not just the selected one.

I added a text box with a picture of what I’m trying to do on the main page.

Just a note, I have a index column on there because for this report, I need to see each transaction separately so I can see just where an account becomes overdrawn, and also because I need to keep it in order by 1-date(ascending), 2-credits(descending), and 3-debits(ascending).

If anyone has any ideas on how to make this more efficient, I would greatly appreciate it.

Thanks,
LRSimmonsrun bal question.pbix (95.0 KB) run tot beg bal.xlsx (499.7 KB)

Hi @LRSimmons, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Not adhering to it may sometimes cause delay in getting an answer.

Hi @LRSimmons,

I have added a measure “Sum of Beg Balance” And made a modification in yours cum-cred measure.

I guess this is what you are trying to achieve. Lemme know if you understand the formula else i will explain.

Regards,
Hemant

run bal question.pbix (95.0 KB)

2 Likes

@Hemantsingh ,

:+1: - creative approach to the solution on this one.

One suggestion for the future. When you provide solutions, keep in mind that you are not just responding to the original questioner, but also building a knowledge base upon which future users with similar questions can access and apply your solution. Thus, if you have a couple of key measures that were the focal point of your solution approach, it’s always good to include those (formatted, preferably) within the body of the post to let people see immediately what you did, rather than requiring them to dig through the PBIX.

Thanks.

  • Brian

HI @BrianJ

I understand your point and agree to your suggestion regarding formatting and explanation.I will keep your points in mind when i reply to other posts in near future. Thanks for your thumbs up to my solution. I appreciate it.

Best Regards,
Hemant

1 Like

Thank you @Hemantsingh !
That is just perfect, and exactly what I needed! :+1:
Here are the measures, for everyone’s reference:

Sum of Beg Balance = 
    VAr A = VALUES(BegBal[Acct]) 
 Return
  CALCULATE(Sum(BegBal[Beg Bal]),filter(Main1,Main1[Stmt Acct #] in A))

CumCred-Deb = [Sum of Beg Balance]+CALCULATE([Cred - Deb],
   ALLSELECTED(Main1),
  FILTER(
     ALLSELECTED(Main1[Index]),
    ISONORAFTER(Main1[Index], MAX(Main1[Index]),DESC)))

Thanks again,
L. R.

1 Like