topic Re: Calculate days outstanding for invoices in DAX Commands and Tips

Calculate days outstanding for invoices

kruizing — Wed, 16 Sep 2020 08:44:43 GMT

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

Re: Calculate days outstanding for invoices

jthomson — Wed, 16 Sep 2020 11:42:16 GMT

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

Re: Calculate days outstanding for invoices

Anonymous — Wed, 16 Sep 2020 12:00:16 GMT

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

Re: Calculate days outstanding for invoices

kruizing — Thu, 17 Sep 2020 09:33:00 GMT

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.

Re: Calculate days outstanding for invoices

Anonymous — Thu, 17 Sep 2020 10:29:03 GMT

Invoices is a dimension, so no duplicates are allowed. Duplicates can only exist in fact tables. This is one of the golden rules of dimensional modeling. You get -1 because you are violating the rules.

Re: Calculate days outstanding for invoices

kruizing — Wed, 07 Oct 2020 13:27:19 GMT

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?

Re: Calculate days outstanding for invoices

Anonymous — Wed, 07 Oct 2020 14:22:07 GMT

If you still get -1, then you know why - it's all in the code. If more than 1 invoice is visible in the current context, you'll get -1.

Please read this (https://docs.microsoft.com/en-us/power-bi/guidance/star-schema) to know how to correctly model data for Power BI.

If you can, please place a link to a PBI file here so that I can take a look. You don't have to have real data in it. Just put some data, even fully artificial, that is REPRESENTATIVE of the problem at hand. I'll take a look.

Cheers.