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
QuantusPools
Frequent Visitor

Average Days to Pay by Customer

Hello Everyone,

 

I am trying to figure out which percentage of customers we have that PAY within a given period (1-30, 31-60, 61-90, 91-121,121+).  Since there's several work orders, I am taking the average of the DATEDIFF(INVOICE DATE, PAYMENT DATE, DAY).  I then want to put each DISTINCT customer into the aformentioned aging categories.  

 

My problem is I have an aging column using the following formula.

 

Aging =
VAR DATEDIFF = DATEDIFF('Invoice Register'[InvoiceDate], 'Invoice Register'[PaymentReceived],DAY)

RETURN

If(DATEDIFF <=0, "Not Due",
If(DATEDIFF > 0 && DATEDIFF <= 30, "1-30",
IF(DATEDIFF >=31 && DATEDIFF <=60,"31-60",
IF(DATEDIFF >=61 && DATEDIFF <=90,"61-90",
IF(DATEDIFF >=91 && DATEDIFF <=120,"91-120",
"121+")))))
 
Then I have a Days to Pay Invoice column
Days to Pay Invoice = if(isblank('Invoice Register'[PaymentReceived]),
datediff('Invoice Register'[InvoiceDate],today(),day),
datediff('Invoice Register'[PaymentReceived],'Invoice Register'[InvoiceDate],day))*-1
 
I cannot get a formula to work where I take the average of the "Days to Pay Invoice" and put it into the buckets of aging.  Help!
 
13 REPLIES 13
Reenu12
New Member

Hi i need the soution for same.If you can help me ASAP.
Thanks

Hi,

Share some data, explain the question and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

 

OPEN INVOICES

Overdue by

0-30 days

30-60 days

60-90 days

90+ days

 

How many days late payment average per customer

 

Those are the categories you want to see in the end result.  Share the raw dataset to work with.  Give a proper explanation.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

I am trying to figure out which percentage of customers we have that PAY within a given period (1-30, 31-60, 61-90, 91-121,121+)

and How many days late payment average per customer

Paste the raw data in a format that can be pasted in an MS Excel file.  No private message.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Reenu12_0-1690950790152.png

some my dates are showing in -Minus thast why its not showing It comes between 31-60 .
becoz the customer not paid yet and the value is o .how can I remove the minus so it will give me the days it comes in. for eg there is one overdue -43 .it should comes between 31-60. it showing no due .because I passed the condtion showing below

Reenu12_1-1690951003313.png

 

I sent you a msg Ashish.

jdbuchanan71
Super User
Super User

@QuantusPools 

Give something like this a try.

Aging Bucket =
VAR _AvgDays =
    CALCULATE (
        AVERAGE ( 'Invoice Register'[Days to Pay Invoice] ),
        ALLEXCEPT ( 'Invoice Register', 'Invoice Register'[Customer Number] )
    )
RETURN
    SWITCH (
        TRUE (),
        _AvgDays <= 0, "Not Due",
        _AvgDays <= 30, "1-30",
        _AvgDays <= 60, "31-60",
        _AvgDays <= 90, "61-90",
        _AvgDays <= 120, "91-120",
        "121+"
    )

 

If this solves your issues please mark it as the solution. Kudos 👍 are nice too.

@jdbuchanan71 

 

Thanks for the response.  I don't think its doing what I want it to be doing.  It is basically marking almost all clients as "Not due" when there should be almost none in that status.  Let me outline what I want this to do.

 

1 - Take the average of days it took to pay the invoice.  I want the average since there are several work orders.  There are some work orders paid in 1 to 30 and some paid in 60 to 90 etc.  If i don't take the average, I can have the same customer in multiple aging buckets.  I don't want this.  I want to get a baseline for a customer's payment history.

2- Take each UNIQUE customer average and drop into aging bucket as described above. 

 

I'm not sure if my logic is correct in making this formula but I would imagine:

 

1. I need to create a filter on the customer that essentially creates a new table.  (does CALCULATETABLE work for this ?)

2. Then average the days it took them pay their invoices in this new 'virtual' table.

3. Drop each value into one of the aging buckets to see payment history.  I need to be able to filter this by year / month. 

 

Let me know if that helps clarify the problem.  

@QuantusPools 

Could you perhaps share your .pbix?  When I test against a sample it is returning the results I expect.

AgingBuckets.jpg

The formula does what you describe.  Filter to the table to the customer for that row and calculate the average days to pay invoice for all that customers invoices then assigns the bucket.

@jdbuchanan71 

 

Can I share it privately?  I can definately filter down per customer but I would like it to work higher level.  Basically, I would like it to show me what percentage of TOTAL customers (averaged) fall into each bucket.  With this currently, I can filter down per customer and see which percentage of invoices are paid in each aging bucket.  

For that you can use the aging bucket on the invoice table from the column and a DISTINCTCOUNT(Table[customer number]) measure.   Yes, you can share it privately, if you load it to OneDrive or DropBox you can send me a PM with the link.

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.