How to calculate running balance with similar dates ?

[kiranbchitari]

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

 Fig. 2

I am attaching the .pbix file.

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

[Ashish_Mathur · Accepted solution]

Hi,

Try this measure

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

Hope this helps.

[kiranbchitari]

@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)?

[Ashish_Mathur]

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

[kiranbchitari]

@Ashish_Mathur Thanks for your help.