Forum Discussion
EARLIER function in Power BI
Hi all, I am new to this, I would like the 'Current Balance' by each line by date (from earliest to latest) and instead of aggregation by dates (items in red is not what I wanted - I need individual line balances). I'm not sure which field should I use in my EARLIER function if not Invoice Date. I can't use Invoice No either since there will be same invoices but different amount. Line 1 calculation for 'Current Balance' is slightly different from the rest. Line 1 'Current Balance' should calculate Line 1 'Beginning Balance' - Line 1 'Amount', while the remaining lines 'Current Balance' calculation should be previous line 'Current balance' - current line 'Amount'.
Thanks and appreciate your help!
Current script:
Current Balance = -1 * SUMX(FILTER(Table1, Table1[Invoice Date ] <= (EARLIER(Table1[Invoice Date] )) ),Table1[ Amount]-Table1[Beginning Balance])
Current results:
| Invoice No | Invoice Date | Amount | Index | Beginning Balance | Current Balance |
| 2300000008 | 4/2/2019 | -235534.82 | 1 | -3269036.34 | -3033501.52 |
| 1200000019 | 4/6/2019 | 251950.76 | 2 | 0 | -3285452.28 |
| 1200000030 | 4/12/2019 | 203745.12 | 3 | 0 | -3489197.4 |
| 1200000051 | 4/20/2019 | 93364.99 | 4 | 0 | -3582562.39 |
| 2300000040 | 4/24/2019 | -504408.7 | 6 | 0 | -2798926.49 |
| 2300000040 | 4/24/2019 | -279227.2 | 5 | 0 | -2798926.49 |
| 1200000076 | 4/26/2019 | 76893.41 | 7 | 0 | -2875819.9 |
| 2300000087 | 4/30/2019 | -10185 | 8 | 0 | -2848213.7 |
| 2300000088 | 4/30/2019 | -17421.2 | 9 | 0 | -2848213.7 |
| 1200000094 | 5/4/2019 | 399520.88 | 10 | 0 | -3247734.58 |
| 2300000202 | 5/24/2019 | 82225.93 | 11 | 0 | -2910729.16 |
| 2300000202 | 5/24/2019 | -455695.88 | 13 | 0 | -2910729.16 |
| 1200000153 | 5/24/2019 | 36464.53 | 12 | 0 | -2910729.16 |
| 1200000175 | 5/31/2019 | 72929.06 | 14 | 0 | -2983658.22 |
| 2300000268 | 6/6/2019 | -170258.4 | 15 | 0 | -2813399.82 |
| 2300000340 | 6/26/2019 | -399520.88 | 17 | 0 | -2405926.88 |
| 2300000340 | 6/26/2019 | -7952.06 | 16 | 0 | -2405926.88 |
Anonymous
You can use the INDEX column to get the running current balanceCurrent Balance = SUMX( FILTER( Table7 , Table7[Index] <= EARLIER(Table7[Index] )), Table7[Amount] - Table7[Beginning Balance] )________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
2 Replies
Anonymous
You can use the INDEX column to get the running current balanceCurrent Balance = SUMX( FILTER( Table7 , Table7[Index] <= EARLIER(Table7[Index] )), Table7[Amount] - Table7[Beginning Balance] )________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂