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.
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 |
Solved! Go to Solution.
@Anonymous
You can use the INDEX column to get the running current balance
Current 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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Anonymous
You can use the INDEX column to get the running current balance
Current 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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thanks for the tip! Yes, this works according to what I need after I sort my Index column in ascending order.
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 |
---|---|
110 | |
97 | |
78 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |