topic Re: Aging balnace for customer in DAX Commands and Tips

Aging balnace for customer

Sarutra — Sat, 22 Jan 2022 13:54:00 GMT

Hello,

I am a beginner.

Can't create an aging balance customer.

example:

Customer

Total balance

Age balance<0 days

Age balance>0 days

Age balance 0-15 days

Age balance 16-30 days

Age balance 31-45 days

Age balance 49-999 days

Slicer:

year

month

day

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

thanks

Arturas

Re: Aging balnace for customer

littlemojopuppy — Sat, 22 Jan 2022 14:41:55 GMT

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!

Re: Aging balnace for customer

Sarutra — Sat, 22 Jan 2022 15:51:17 GMT

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=y22bzY

Re: Aging balnace for customer

Sarutra — Sun, 23 Jan 2022 12:54:59 GMT

Hello,

how to delete checked values

thanks

Arturas

https://sarutra-my.sharepoint.com/:w:/p/powerbi/ERFnQe7WZkVHmqfNqWKYG_IBCwQyBulmM-TwxpMuOj0K_g?e=DJiEeF

https://sarutra-my.sharepoint.com/:u:/p/powerbi/EfvKTdqrIfpGvMetSXkXlHABeQXHQ3_yVJad-FX5ZsWkbw?e=Syy6RF

Re: Aging balnace for customer

DimaMD — Sun, 23 Jan 2022 17:11:59 GMT

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 )

Re: Aging balnace for customer

Sarutra — Sun, 23 Jan 2022 18:12:41 GMT

Hello,

a big thank you, its work

Arturas

Re: Aging balnace for customer

Sarutra — Sat, 05 Feb 2022 13:51:16 GMT

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=H4akmR

Re: Aging balnace for customer

littlemojopuppy — Thu, 10 Feb 2022 02:38:41 GMT

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 😉