The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
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.
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.
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.
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
I sent you a msg Ashish.
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.
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.
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.
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
80 | |
78 | |
43 | |
37 |
User | Count |
---|---|
158 | |
111 | |
64 | |
59 | |
54 |