DAX to count the number of times, for each customer, an arrears amount moves 3 consecutive months

[Water]

Power Bi Experts, please help with the correct DAX for the below calculation?

Summary: I want to flag non-paying customers becoming a risk - by counting the times an unpaid arrears amount for each customer “moves” over three consecutive months.

Monthly I receive a dataset with customers with their amounts in arrears. The arrear amounts are grouped in one of four monthly progressive groups: 30-59 days, 60-89 days, 90-119 days, and over 120 days.

Let say John owes $30 as part of the "30-59 days" group in April 2023 but then doesn’t pay it, and that $30 moves to the "60-89 days" arrears in May 2023, and then to the "90-119 days" group in June 2023, it will probably end up in the "over 120 days" in July 2023.

If this same patterns continues for all his arrear amounts as times passes, moving from "30-59 days" to "90-119 days" over three consecutive months, John is to be flagged as a risk to be monitored more closely.

So my idea is to count the number of times, for each customer, a specific arrears amount moves from the:

• "30-59 days" category to the "90-119 days" category (with each such movement counting as 1).

Question: Please help me with the DAX to accomplish the above?

The aim is to create a table ranking the customers with counts per year sorting from highest count to lowest. Ideal output will look something like this:

The PBIX file is here with the example date as copied below. (Has a date table.) 

To calculate for Peter, the logic is as follows:

Thank you very much!

W

[Anonymous · Accepted solution]

Hi @Water ,

I'm afraid your requirements might not be achievable through DAX alone. I would recommend first formatting the data in Power Query to better align with your needs. I've made a test for your reference:

1\ I assume there is a excel table

2\Edit Query

a)SortByCustomer Name and Date

b)add an index column

c)Add a count column to count rows that meet your neeeds

    #"Added Custom" = Table.AddColumn(#"Added Index", "CountNumber", each if [Index] < 3 or [#"$ Amount of  90 - 119 Days in Arrears"]= null  then 0 else if [#"$ Amount of  90 - 119 Days in Arrears"]=Table.Column(#"Added Index", "$ Amount of 60 - 89 Days in Arrears"){[Index]-1} and [#"$ Amount of  90 - 119 Days in Arrears"]=Table.Column(#"Added Index", "$ Amount of30 - 59 Days in Arrears"){[Index]-2} then 1 else 0),

d)Change type

    #"Added Custom" = Table.AddColumn(#"Added Index", "CountNumber", each if [Index] < 3 or [#"$ Amount of  90 - 119 Days in Arrears"]= null  then 0 else if [#"$ Amount of  90 - 119 Days in Arrears"]=Table.Column(#"Added Index", "$ Amount of 60 - 89 Days in Arrears"){[Index]-1} and [#"$ Amount of  90 - 119 Days in Arrears"]=Table.Column(#"Added Index", "$ Amount of30 - 59 Days in Arrears"){[Index]-2} then 1 else 0),

The whole power query code

let
    Source = Excel.Workbook(File.Contents("C:\Users\Bof\Desktop\Book111.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Account Number", Int64.Type}, {"Customer Name", type text}, {"Date", type date}, {"$ Amount of30 - 59 Days in Arrears", Int64.Type}, {"$ Amount of 60 - 89 Days in Arrears", Int64.Type}, {"$ Amount of  90 - 119 Days in Arrears", Int64.Type}, {"$ Amount of Over 120 Days in Arrears", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Customer Name", Order.Ascending}, {"Date", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "CountNumber", each if [Index] < 3 or [#"$ Amount of  90 - 119 Days in Arrears"]= null  then 0 else if [#"$ Amount of  90 - 119 Days in Arrears"]=Table.Column(#"Added Index", "$ Amount of 60 - 89 Days in Arrears"){[Index]-1} and [#"$ Amount of  90 - 119 Days in Arrears"]=Table.Column(#"Added Index", "$ Amount of30 - 59 Days in Arrears"){[Index]-2} then 1 else 0),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"CountNumber", Int64.Type}})
in
    #"Changed Type1"

3\Edit in report

Best Regards,

Bof

[SachinNandanwar · Accepted solution]

You can do it through DAX
First create a CalculatedTable :

and then a measure in that table

and then create a final output table

Output :

File attached here : https://easyupload.io/t42nqk

[Water]

Thank you so much, @SachinNandanwar ! 

You solution works fantastically. What I really appreciate is how well this works on a massive dataset. 

Sorry for taking so long to get back to you. There was some problem which took me a while to figure out. 

(May I be so forward to ask you to please have a look at my other question and see if you can suggest something just as brilliant?)

Thanks and best regards, 

W

[SachinNandanwar]

No worries..Glad to help and thank you for the feedback.

[Anonymous · Accepted solution]

Hi @Water ,

I'm afraid your requirements might not be achievable through DAX alone. I would recommend first formatting the data in Power Query to better align with your needs. I've made a test for your reference:

1\ I assume there is a excel table

2\Edit Query

a)SortByCustomer Name and Date

b)add an index column

c)Add a count column to count rows that meet your neeeds

    #"Added Custom" = Table.AddColumn(#"Added Index", "CountNumber", each if [Index] < 3 or [#"$ Amount of  90 - 119 Days in Arrears"]= null  then 0 else if [#"$ Amount of  90 - 119 Days in Arrears"]=Table.Column(#"Added Index", "$ Amount of 60 - 89 Days in Arrears"){[Index]-1} and [#"$ Amount of  90 - 119 Days in Arrears"]=Table.Column(#"Added Index", "$ Amount of30 - 59 Days in Arrears"){[Index]-2} then 1 else 0),

d)Change type

    #"Added Custom" = Table.AddColumn(#"Added Index", "CountNumber", each if [Index] < 3 or [#"$ Amount of  90 - 119 Days in Arrears"]= null  then 0 else if [#"$ Amount of  90 - 119 Days in Arrears"]=Table.Column(#"Added Index", "$ Amount of 60 - 89 Days in Arrears"){[Index]-1} and [#"$ Amount of  90 - 119 Days in Arrears"]=Table.Column(#"Added Index", "$ Amount of30 - 59 Days in Arrears"){[Index]-2} then 1 else 0),

The whole power query code

let
    Source = Excel.Workbook(File.Contents("C:\Users\Bof\Desktop\Book111.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Account Number", Int64.Type}, {"Customer Name", type text}, {"Date", type date}, {"$ Amount of30 - 59 Days in Arrears", Int64.Type}, {"$ Amount of 60 - 89 Days in Arrears", Int64.Type}, {"$ Amount of  90 - 119 Days in Arrears", Int64.Type}, {"$ Amount of Over 120 Days in Arrears", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Customer Name", Order.Ascending}, {"Date", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "CountNumber", each if [Index] < 3 or [#"$ Amount of  90 - 119 Days in Arrears"]= null  then 0 else if [#"$ Amount of  90 - 119 Days in Arrears"]=Table.Column(#"Added Index", "$ Amount of 60 - 89 Days in Arrears"){[Index]-1} and [#"$ Amount of  90 - 119 Days in Arrears"]=Table.Column(#"Added Index", "$ Amount of30 - 59 Days in Arrears"){[Index]-2} then 1 else 0),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"CountNumber", Int64.Type}})
in
    #"Changed Type1"

3\Edit in report

Best Regards,

Bof

[Water]

Thank you again for you help.

Unfortunately your solution is not working, but this is because my example data was not sufficient in replicating the real-life dataset. I am sorry, this is my mistake. Your solution will only work where there is one row per customer per month as was per my initial example data. As I was looking more closely at my actual data, I saw each customer might have more than one entry per month as per various Rate Codes or Contract numbers, so more than one amount in each of the arrears categories. Below you can see e.g. Japie with multiple rows just in July 2024. (Or here is Japie's complete record in Excel with two other customers.)

This means the indexing part of the solution does not work.

Also, with more than 6 million rows, it takes very long to execute your solution. The other solution provided by SachinNandanwar afterwards seems to work better, and I am busy testing it.

Thanks again for you effort.

Best regards, 

W

[Water]

Thank you very much! Really appreciate your help and time spend on this. I am excited to study your solution and test it on my real dataset. Will definately accept as solution if all works well. Please give me a couple of days. Best wishes.