Please help with the correct DAX to achieve the below? Not sure if I need to modify the date table or model to make this work?
Monthly I receive a dataset with customers with their amounts in arrears. The arrear amounts are grouped in one of four progressive groups, including an “over 120 days” category.
I want to identify high risk customers - by counting the number of times the cumulative arrears total for each customer increases over consecutive months in an “over 120 days” category.
If the arrears total in the “over 120 days” keeps on increasing month after month, it means the customer is not paying and should be monitored more closely.
Question: Please help with the DAX to count the number of times, for each customer, the cumulative total in the “over 120 days” arrears category increases over consecutive months – where each consecutive increase counts as 1.
The hope is to create a table ranking the customers with counts per year sorting from highest count to lowest. See below ideal outcome as per the attached pbix file with the data.
Thanks for the interesting suggestion. I have tried 5 different EDNA proposed solutions now, but none worked. I have accounted various problems, ranging from not addressing the question, DAX not working, not representing my data correctly, to missing steps in the solution. Probably a great tool in the future, but at present still in its learning phase.
I will have another look tomorrow, but at present I don’t feel too excited about it.
Here is a solution that gets the results you are looking for.
I was a little redundant with the measures, so they can be cleaned up a bit, but it works.
I added a new column that calculates the value of last month, for this customer, this year, and compared it to this month’s value. If this month > last month, then 1, else 0.
Then counted the 1s.
Cumulative Increase =
VAR thismonth = CALCULATE(SUM(Arrears[$ Amount of Over 120 Days in Arrears]), FILTER(Arrears, Arrears[YearMonth] = (EARLIER(Arrears[YearMonth]))), FILTER(Arrears, Arrears[Customer Name] = EARLIER(Arrears[Customer Name])), FILTER(Arrears, Arrears[YEAR] = EARLIER(Arrears[YEAR])))
VAR lastmonth = CALCULATE(SUM(Arrears[$ Amount of Over 120 Days in Arrears]), FILTER(Arrears, Arrears[YearMonth] = (EARLIER(Arrears[YearMonth])-1)), FILTER(Arrears, Arrears[Customer Name] = EARLIER(Arrears[Customer Name])), FILTER(Arrears, Arrears[YEAR] = EARLIER(Arrears[YEAR])))
RETURN
IF(thismonth>lastmonth, 1, 0)
Thank you very much, Thiago! Really appreciate your help and time spend on this. I am excited to study your solution and test it on my real dataset. Please give me a day or two days. Best wishes.
We are getting closer to the solution, however, there is one issue I picked up. It is as if the “VAR thismonth = CALCULATE(SUM…”-part is not working?
I attached another example here to show the problem. It is a Power BI file with your suggested solution applied on a different dataset.
Applying your solution I get (see in attached file):
However, I am expecting 5 for 2023 (or maybe 4 if the there is no month-over month increase here) and 9 for 2024.
It looks like all rows in the dataset are counted, not the number of month-over-month increases based on the sum of the “Over 120 Days”. (There are 62 rows in the dataset, but 9 months in 2024 up till September, each month with an higher total as the previous month.) (Dataset in Excel available here.)
The calculation logic should be (copied from the attached pbix-file):
William,
attached the pbix with a possible pattern you can use flag japie.pbix (59.3 KB)
I uplaoded your initial table and grouped the date by month and total “over 120 days” then used the offset() to calculate the previous month value for “over 120days” then a simple calc if greater then 1 else 0.
so take a look how the query load the data in power BI, only the column over 120 days is relevant for the calculation
hope this helps
kind regards
Roger
In this case it didn’t work because there is more than one value for each month.
Maybe group it in PowerQuery like @Roger mentioned, or use a SUMMARIZE function.
