Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all!
I have 3 tabels with data:
- Invoices > all the data about the invoice we send to our customers
(invoices[date])
(invoices[amount])
(invoices[customernumber])
(invoices[invoicenumber])
- Payments > all the data about the payments we receive
(payments[date])
(payments[amount])
(payments[customernumber])
(payments[invoicenumber])
- Calander > all the dates ect.
(calander[date])
I would like to calculate the days outstanding for the invoices that have not been paid on any date. I also would like to categorise them into categories like 0 - 30 days outstanding, 31-60 days outstanding ect
I have searched through the community but could not find the right sollution. Who can help me?
Kind regards en thank you very much in advance!
Simon Kruizinga
// The Calendar should NOT be connected
// to Payments. Payments should be hidden.
// All calculations on Payments should
// be exposed solely through measures.
// The Calendar will be connected to
// Invoices on the Invoices[Date] field.
// of course Invoices will be connected
// to Payments on the invoice number.
[Days Outstanding] =
// This will return the days
// since the invoice date
// until TODAY() if the invoice
// has not had any payment against it.
// If it has had at least one payment,
// BLANK will be returned. If there are
// many invoices from Invoices visible
// in the current context, -1 will be
// returned (you can then change it to
// any value you think makes sense).
var __today = TODAY()
var __onlyOneInvoiceVisible =
HASONEVALUE( Invoices[InvoiceNumber] )
var __result =
if( __onlyOneInvoiceVisible,
var __noPaymentsPresent =
CALCULATE(
ISEMPTY( Payments ),
ALL( Payments ),
VALUES( Invoices[InvoiceNumber] )
)
var __daysOutstanding =
if( __noPaymentsPresent,
// get the date of the invoice
// and find the number of days
// since then until __today
var __invoiceDate =
SELECTEDVALUE( Invoices[Date] )
return
INT( __today - __invoiceDate )
)
return
__daysOutstanding,
// output when there are many invoices in scope
-1
)
return
__result
It's now easy using the measure above and a disconnected table with the age brackets to calculate the number of invoices (dynamically) that fall into each of these brackets.
Hi!
Thank you very much for your contribution!
I have tried your sollution but unfortunatly it didn't worked as I hoped. I only get the result of -1
Could this have something to do with the fact that multiple records can exist for one invoice number? Also in the table of payments, multiple records can exist for one invoice.
I am sorry for violating the rules. I'm pretty new into BI , please be patient with me 😊
I have created an extra table for the invoices with only 1 unique record for each invoice. In this table I have the same data as before. All the movements on the invoices and payments are in seperate tables. I have adjusted your script in one of your previous posts. Unfortunatly without any result.
What am I doing wrong?
Assuming that there's some sort of relationship between the two tables, then something like this as a conditional column:
Age = if(Payments[date] <> blank(), datediff(Invoices[date],today(),day),blank()) ought to work
You can then use groupings to get 0-30, 31-60 etc, or something like
AgeGroup = if([Age]<31,"0-30"),if([Age]<61,"31-60","61+"))
Personally I'd do this in Power Query, but that might be a bit complicated if you get more than one payment on an invoice
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
44 | |
32 | |
30 | |
18 | |
17 |