Days to payment received visualization in ongoing months

[Veronica1]

I’m working on a report where the user wants to see the No of days to the payment received of an invoice in the current month. For that, I created a measure to calculate the days to payment something like this:

Avg Days to Payment =
VAR PymtDate = SELECTEDVALUE(InvoiceTable[PaymentDate])
Return
AVERAGEX(InvoiceTable, DATEDIFF(
MIN(InvoiceTable[InvoiceDate]),
COALESCE(PymtDate, TODAY()), /
DAY
)
)

which gives me a table with the No of days to payment as below:

I want to show the Avg Days to payment in a column chart where those invoices that there is a payment date are shown in the invoice date and those invoices where there is not a payment received are shown in the current months…

For example the 133 days should be shown in the ongoing month.

Has Anyone been able to do anything similar? I have been searching for weeks and I couldn’t find anything similar.

Thank you.

[deltaselect]

Dear @Veronica1

Welcome on the forum !

Answer: please receive hereby an answer on your question with some comments,
PBIX and Excel source data included:
Avg days paid 2 presentations.pbix (87.5 KB)
Invoice and payment dates.xlsx (11.2 KB)

The question (as understood correctly):

• avg days to paýment for paid invoices per reporting period, based upon invoice date,
• avg days to payment for ALL NON-paid invoices in the current reporting period

A solution is to add a calculated column (“Adjusted invoice date for reporting”) to adjust the invoice date: leave the original invoice date when paid, but enter the Reporting End Date as (invoice) date for non-paid invoices. Use this calculated date column as the base for the reporting periods, which, as a result, will show all unpaid invoices in the most recent cq. current reporting period. (the calculation for “Avg Days to Payment” is not affected by this calc. column, see also comment 1 below)

Adjusted invoice date for reporting = IF( ISBLANK( InvoiceT[Payment date]), [Reporting end date], InvoiceT[Invoice date])

Some comments:

1. In order to calculate the correct avg days to payment for (the total of) multiple invoices, the measure must be adusted to:

   Avg Days to Payment = 
   AVERAGEX(InvoiceT, 
               DATEDIFF( InvoiceT[Invoice date], COALESCE( InvoiceT[Payment date], [Reporting end date]), DAY))
   

   Recommended is to use a reporting end date measure, like Reporting end date = date( 2023, 10,12)

2. Reporting this way causes the “Avg Days to Payment” to increase dramatically in the current period, as it contains all non paid-invoices, with high values for “Avg Days to payment” (Invoice date - Reporting date), ***which in my opinion is incorrect.

   I would recommend to report “Avg Days to Payment”, based only upon paid invoices, and use the invoice date as the base for the reporting periods.

   Outcome of the requested reporting:

   

3. Aging reporting is often used for unpaid invoices, which is explained in detail at Aged Trial Balance, sub Advanced dynamic grouping modelling and formula techniques and sub Visualization options of the EDNA course Financial Reporting With Power BI https://app.enterprisedna.co/app/learning-courses?id=11

If you have further questions, please let me know.

Kind regards, Jan van der Wind