Calculating the number of days outstanding

[gra_197]

Hi,

I need some help on calculating the number of days a purchaser invoice remains outstanding.

Daily I receive an excel ss with a number of outstanding invoices.

This spreadsheet contains the name of the debtor, invoice number,  date invoice is to be paid, todays date and the number of days the invooce is outstanding. For every day the invoice remains outsranding, the number of days will increase.

What Im looking to do is address late payers and show them the length of time invoices remained outstanding.

Ive been able to create a table visual with the Name,invoice number,actual date of invoice and latest date outstanding. What I need in this visual is another column that shows the difference between the actual settlement date and the latest recorded date outstanding. Can someone confirm the best way to do this.?

[krishnakanth240]

Hi @gra_197 

Measure

Latest Outstanding Date =

MAX ( Invoices[Snapshot Date] 

)

Measure 

Effective End Date =

VAR SettlementDate =

    MAX ( Invoices[Settlement Date] )

VAR LatestOutstanding =

    MAX ( Invoices[Snapshot Date] )

RETURN

    COALESCE ( SettlementDate, LatestOutstan

ding )

Measure

Days Outstanding =

VAR InvoiceDate =

    MIN ( Invoices[Invoice Date] )

VAR EndDate =

    [Effective End Date]

RETURN

    DATEDIFF ( InvoiceDate, EndDate,

 DAY )

[cengizhanarslan · Accepted solution]

Assuming:

• One row per invoice per day in the Excel file

• You already have Invoice Date and Outstanding Date in the model

Measure:

Days Outstanding =
VAR InvoiceDate =
    MIN ( Invoices[InvoiceDate] )
VAR LastOutstandingDate =
    MAX ( Invoices[OutstandingDate] )
RETURN
DATEDIFF ( InvoiceDate, LastOutstandingDate, DAY )

Use this in a table visual with Debtor and Invoice Number.

If the invoice is still unpaid, Replace "LastOutstandingDate" with TODAY():

VAR EndDate =
    IF ( MAX ( Invoices[IsPaid] ) = TRUE(), MAX ( Invoices[OutstandingDate] ), TODAY() )

Then use "EndDate" in "DATEDIFF".

[gra_197]

@cengizhanarslan thanks so much, worked a treat.