Evolution of aging receivables

[Anonymous]

Dear all,

Based on my dataset, I'm trying to show a graph showing the evolution of aging receivables. I have found several topics in the community related to this subject, but none of them giving me the solution I need (unless I missed a post).

My aim: A graph.

The X-axis should be all dates from 1 Jan 2017 till today grouped per month. The Y-axis should be the amount of open debt.

The graph should show several lines, one for each type of overdue. One line should indicate the amount of debt NOT overdue for all dates on the X-axis. Other lines should refer to buckets: 1 - 30 days overdue, 31 - 60 days overdue, 61 - 90 days overdue and 90+ days overdue.

I can't share my data source as it's confidential data but I'll add a draft table instead to show you the data I have to start from. 

If Payment Date is blank, that means that the payment hasn't occurred yet.

Invoice number	Invoice Date	Due date	Payment Date	Amount
aaa	05/01/2017	31/01/2017	02/02/2017	100
bbb	06/06/2018	30/06/2018	05/08/2018	200
ccc	09/04/2019	30/04/2019	11/08/2019	150
ddd	29/09/2020	15/10/2020		50

In my graph, invoice aaa should not show as overdue before 31 Jan 2017, but should show as overdue as from 1 Feburary 2017. 

Invoice ccc should show +90 days overdue as from 1 August 2019, etc.

Can someone please help me?

Thank you!

[amitchandak]

@Anonymous ,Based on what I got

You have created overdue days as a measure

example

Over due Day = datediff([Due date],[Payment Date],day)

And create an independent table of the bucket and create a measure to count based on this bucket

You can my Video on a similar topic how to do that:https://youtu.be/CuczXPj0N-k

Please check

https://www.daxpatterns.com/dynamic-segmentation/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

[Anonymous]

Dear @amitchandak,

Currently, I haven't found a good working measure yet as the critical data in my graph is the evolution of bad debt over time. Unfortunately, your video is missing this key nuance that I need for my data so I'm looking for help how to best set up this model and which measures to use exactly based on the table examples that I provided.

Thank you!

Best regards,

[v-xuding-msft]

Hi @Anonymous ,

I'm a little confused that how it shows like in gragh. I created a sample like below, it returns the same amount for the whole dates. You could modify it by yourself or please share mode details with me. 

• A new date table

Date = CALENDAR(DATE(2017,1,1),TODAY())

• Measures:

Count overdue day = 
var paymentdate = MAX('Table'[Payment Date])
return
DATEDIFF(MAX('Table'[Due date]),DATE(YEAR(paymentdate),MONTH(paymentdate),1),DAY)

NOT overdue = CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Due date] >= TODAY()))

1 - 30 days overdue = CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Date'),[Count overdue day]>0 && [Count overdue day]<=30 ))

Others are same as "1 - 30 days overdue" measure. Please download the pbix to check it.

[Anonymous]

Dear @v-xuding-msft ,

Thank you for your response!

However, this static line is not what I'm looking for.

I'm not sure how to add pictures to the message, and I can't share my model due to confidentiality reasons, but I'll do my best explaining what the graph needs to look like.

The X-axis should have all dates from the date table.

The Y-axis should show the amount overdue at those dates.

I'll explain what the graph needs to look like for 1 - 30 days overdue.

At 1 Jan 2017, amount overdue is 0 as no amount is overdue at that point.

At 1 Feb 2017, 100 is overdue as the invoice of 5 January 2017 got overdue at this point.

At 1 July 2018, 200 should show overdue as the invoice of 6 June 2018 got overdue. The invoice of 5 January 2017 should no longer be counted as this one got paid in the meanwhile.

Etc.

Does this help?

Thank you!

[v-xuding-msft]

Hi @Anonymous ,

Sorry for late back.  I use a Gantt chart to implement your requirement. Hope I understand correctly this time .

• Create calculated columns

Duration = 
var first_day = IF(ISBLANK('Table'[Payment Date]),BLANK(),DATE(YEAR('Table'[Payment Date]),MONTH('Table'[Payment Date]),1))
return
DATEDIFF('Table'[Due date],first_day,DAY)

Resource = 
SWITCH(TRUE(),
'Table'[Payment Date] = BLANK(),"not overdue",
'Table'[Duration]<=30, "1 - 30 days overdue",
'Table'[Duration]<=60, "31 - 60 days overdue",
'Table'[Duration]<=90, "61 - 90 days overdue",
'Table'[Duration]>90,  " 90 days overdue"
)

 

For more details about Gantt chart, you could reference this blog:

Visual Awesomeness Unlocked – Gantt chart