cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sarutra
Frequent Visitor

Aging balnace for customer

Hello,

I am a beginner.

Can't create an aging balance customer. 

example:

CustomerTotal balanceAge balance<0 daysAge balance>0 daysAge balance 0-15 daysAge balance 16-30 daysAge balance 31-45 daysAge balance 49-999 days

 

Slicer:

year

month

day

 

https://sarutra-my.sharepoint.com/:u:/p/powerbi/EVy6MFCinmNPqVhc6IuqbWUBnRxwdGNi7uJV9hiEGmoz4Q?e=0bg... 

 

thanks

 

Arturas

 

1 ACCEPTED SOLUTION

Hi @Sarutra 
Try it

Balance Total = 
VAR balans =
 CALCULATE(SUM('sales-payments'[Debet])-SUM('sales-payments'[Credit]),ALL('Calendar'[Date]))
 return
 IF(ISBLANK([running total_DEB]),BLANK(),
 balans
 )

Screenshot_1.png

View solution in original post

7 REPLIES 7
Sarutra
Frequent Visitor

Hello,

a big thank you, its work

 

Arturas

Sarutra
Frequent Visitor

Hello,

I updated the file

calculate 

"Not paid infoice EUR", I no longer have a solution 
 
Arturas

https://sarutra-my.sharepoint.com/:u:/p/powerbi/EVwnz4wKugZAsn418zX0BnoBFjvJ8EMud1N9lwK37MPQIw?e=y22...

littlemojopuppy
Super User
Super User

Hi @Sarutra.  I looked at your file, and you're right: you can't create an aging balance for customers because your data is incomplete.  With an A/R againg analysis, you take all outstanding invoices and determine how old they are.  Unfortunately you can't determine which invoices are outstanding or not.  You would need to have some kind of status on invoices that indicates "paid" or "not paid" in order to do this correctly.

 

Good luck!

Hi @Sarutra 
Try it

Balance Total = 
VAR balans =
 CALCULATE(SUM('sales-payments'[Debet])-SUM('sales-payments'[Credit]),ALL('Calendar'[Date]))
 return
 IF(ISBLANK([running total_DEB]),BLANK(),
 balans
 )

Screenshot_1.png

Sarutra
Frequent Visitor

Hi,

"Aging balance 250-300Days" measure not work correct,maybe you can advise what to change
 
thank you
Arturas

https://sarutra-my.sharepoint.com/:u:/p/powerbi/EaMrK62Pf8RAsYbSfFPSG20B-GeXoJnow2Q06JQFO6HppQ?e=H4a...

 

@Sarutra I apologize for not expanding on this earlier, but I'd like to explain why the first out, first paid model doesn't quite work.

 

Picture you credit card bill...

Date Vendor Amount Description
2-Jan Apple $30 Apple Music, iCloud
3-Jan McDonalds $8.50 Breakfast
4-Jan McDonalds $8.50 Breakfast
5-Jan ABT $1,200 Dryer
6-Jan McDonalds $8.50 Breakfast
7-Jan McDonalds $8.50 Breakfast
8-Jan Drizzly $40 Friday booze

Except you didn't order a dryer from ABT on January 5th.  You're not going to pay that.

 

It's no different for for employer/client.  They're going to want matching of Purchase Order, Proof of Delivery and Invoice.  If you're simply paying down oldest invoice first without regard to whether that invoice is approved for payment, it's not going to be received well.

You need to have some kind of indicator for specific invoices and approved for payment.  It's not quite as simple as the proposed solution suggests.

 

I have a degree in accounting so I know what I'm talking about 😉

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!