Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 Balance
I am attaching the .pbix file.
https://drive.google.com/open?id=1bkWbZxO3gMdswWDEv0bDUcfFpWYrAqqB
Solved! Go to Solution.
Hi,
Try this measure
=CALCULATE(SUM('Sheet1'[Accounted Amount]),FILTER(ALLEXCEPT('Sheet1',Sheet1[Account]),'Sheet1'[Index]<=MAX('Sheet1'[Index])))
Hope this helps.
Hi,
Try this measure
=CALCULATE(SUM('Sheet1'[Accounted Amount]),FILTER(ALLEXCEPT('Sheet1',Sheet1[Account]),'Sheet1'[Index]<=MAX('Sheet1'[Index])))
Hope this helps.
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).
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |