Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

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
Community Champion
Community Champion

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


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors