cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

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 NoInvoice DateAmountIndexBeginning BalanceCurrent Balance
23000000084/2/2019-235534.821-3269036.34-3033501.52
12000000194/6/2019251950.7620-3285452.28
12000000304/12/2019203745.1230-3489197.4
12000000514/20/201993364.9940-3582562.39
23000000404/24/2019-504408.760-2798926.49
23000000404/24/2019-279227.250-2798926.49
12000000764/26/201976893.4170-2875819.9
23000000874/30/2019-1018580-2848213.7
23000000884/30/2019-17421.290-2848213.7
12000000945/4/2019399520.88100-3247734.58
23000002025/24/201982225.93110-2910729.16
23000002025/24/2019-455695.88130-2910729.16
12000001535/24/201936464.53120-2910729.16
12000001755/31/201972929.06140-2983658.22
23000002686/6/2019-170258.4150-2813399.82
23000003406/26/2019-399520.88170-2405926.88
23000003406/26/2019-7952.06160-2405926.88
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: EARLIER function in Power BI

@soulhunting 

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

Fowmy_0-1601372247144.png

________________________

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

 

View solution in original post

2 REPLIES 2
Highlighted
Community Champion
Community Champion

Re: EARLIER function in Power BI

@soulhunting 

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

Fowmy_0-1601372247144.png

________________________

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

 

View solution in original post

Highlighted
Regular Visitor

Re: EARLIER function in Power BI

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

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors