cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kiranbchitari
Frequent Visitor

How to calculate running balance with similar dates ?

I want to calculate the running balance in the attached .pbix file. The problem with the data set is dates are repeated, so I can't use order on the basis of dates, And the data set is having multiple account details, so If I use Index column then after changing the account using filter, the ordering of Index column is also changed, so, that also not works for me, even I tried with RANKX function but I am not able to get the desired running balance( cumulative total).  The cumulative sum of Accounted Amount can give the running balance.

 

I tried with adding index column, sorted on account column. But I am facing an issue that for 2222 Account (Fig. 1)it is showing correct running balance as the index starts from 1, but for 11111 Account(Fig. 2) it is showing wrong value as the index column is not started with 1 this time, it continues with  37. So I tried RANKX function but even that not works. So please help me with any DAX formula which can be used for calculating Running BalanceFig. 1Fig. 1

 

 

 Fig. 2Fig. 2

I am attaching the .pbix file.

https://drive.google.com/open?id=1bkWbZxO3gMdswWDEv0bDUcfFpWYrAqqB

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User III
Super User III

Hi,

 

Try this measure

 

=CALCULATE(SUM('Sheet1'[Accounted Amount]),FILTER(ALLEXCEPT('Sheet1',Sheet1[Account]),'Sheet1'[Index]<=MAX('Sheet1'[Index])))

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User III
Super User III

Hi,

 

Try this measure

 

=CALCULATE(SUM('Sheet1'[Accounted Amount]),FILTER(ALLEXCEPT('Sheet1',Sheet1[Account]),'Sheet1'[Index]<=MAX('Sheet1'[Index])))

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

@Ashish_Mathur

Thanks for your response, is it possible to calculate running balance if I don't have an Index column. I added index column because I thought there is no other option. Is it possible with Date column(Having repeated dates)?

Hi,

 

You are welcome.  If my previous reply helped, please mark it as Answer.  On taking away the Index column, all transactions on the same day will get grouped (So there will be one line item per day).


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur Thanks for your help.Smiley Wink

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors