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)RETURNIf(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 columnDays 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!

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. 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.

Average Days to Pay by Customer | Microsoft Fabric Community

13 Replies

jdbuchanan71
Super User
6 years ago
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.
- QuantusPools
  Frequent Visitor
  6 years ago
  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.
  - jdbuchanan71
    Super User
    6 years ago
    QuantusPools
    
    Could you perhaps share your .pbix? When I test against a sample it is returning the results I expect.
    
    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.
Reenu12
New Member
3 years ago
Hi i need the soution for same.If you can help me ASAP.
Thanks
- Ashish_Mathur
  Super User
  3 years ago
  Hi,
  
  Share some data, explain the question and show the expected result.
  - Reenu12
    New Member
    3 years ago
    
    OPEN INVOICES
    Overdue by
    0-30 days
    30-60 days
    60-90 days
    90+ days
    
    How many days late payment average per customer

Forum Discussion

Average Days to Pay by Customer

13 Replies

Related content

Day to Day average

3 Day Rolling Average - DAX

Backlog average age by day

Average Per day

Rolling average 30 day