EARLIER function in Power BI

[Anonymous]

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

[Fowmy · Accepted 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 🙂

YouTube  LinkedIn

[Anonymous]

Thanks for the tip! Yes, this works according to what I need after I sort my Index column in ascending order.