Downgrades and upgrades (category) change over time

[tomwolswijk]

In my powerBI dashboard I want to show which customers have been downgraded or upgraded over time (month/quarter).

Dataset:

Month	Client ID	ClientName	Category	Amount
Dec 19	100	Jansen	Good	500,000
Dec 19	100	Jansen	Good	100,000
Mar 20	100	Jansen	Bad	500,000
Mar 20	100	Jansen	Bad	100,000
Dec 19	101	Voet	Bad	500,000
Dec 19	101	Voet	Bad	100,000
Mar 20	101	Voet	Good	200,000
Mar 20	101	Voet	Good	100,000
Dec 19	102	Joosten	Good	500,000
Dec 19	102	Joosten	Good	100,000
Mar 20	102	Joosten	Good	50,000
Mar 20	102	Joosten	Good	100,000

Result i'm looking for is a table or graph like (something like it where i can see the down and upgrades):

Results Mar 20	Downgraded
ClientName	Month (Current)	Month (Previous)	Amount (current)	Amount (Previous)
100	Good	Bad	600,000	600,000

Results Mar 20	Upgraded
ClientName	Month (Current)	Month (Previous)	Amount (current)	Amount (Previous)
101	Bad	Good	600,000	300,000

Client 102 I don't want to see in the Report because it has remained in the same category.

Good	1
Middle	2
Bad	3
Doubtfull	4
Loss	5

Can somebody help me with how I can solve this? This involves multiple lines per customer, 5 categories and different reporting months.

Thank you in advance.

[v-zhenbw-msft · Accepted solution]

Hi @tomwolswijk ,

We can create two columns to meet your requirement.

1. Create a rank column to calculate the rank in each group.

Rank = 
RANKX(FILTER('Table','Table'[Client ID]=EARLIER('Table'[Client ID])),'Table'[Date],,ASC,Dense)

2. Then create the Category (previous date)

Category (previous date) = 
CALCULATE(MAX('Table'[Category]),FILTER('Table','Table'[Client ID]=EARLIER('Table'[Client ID]) && 'Table'[Rank]=EARLIER('Table'[Rank])-1))

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[tomwolswijk]

Thank you, worked out exactly what I needed.

I additon to the 2 colomns I made an index 'Category Index' kolomn where I transformed the Category to a number (1 to 5).

Also did this for 'Category (previous date)'. Then compared the index to calculate if category was upgraded or downgraded:

• Downgrade/Upgrade Category= IF(Data[CategoryIndex (Previous reporting date)] > 0 , Data[Category Index (Previous reporting date)] - Data[Category Index] ,0)

Thanks for your time and help.

[Fowmy]

@tomwolswijk 

When category changes from Bad to Good, should it be considered DOWNGRADED?
How about the amount? is it also a deciding factor?

There are some other categories as well at the end, what is the logic to calculate them?

________________________

If my answer was helpful, please mark this post as a solution, this will also help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube
LinkedIn

[tomwolswijk]

The categories are a given value that I receive every month. When something of Good (1) goes down to category 2, 3, 4 or 5. Then it is considered a downgrade. For example, if a client goes from category 3 to category 2 it is an upgrade. The amount may vary from month to month. This does not affect the categories, I just want to report the amount.

So I mainly want to know in what Category the customer was in the previous reporting month. And when it differs from the current month. Then determine whether it was downgrade or upgrade.

Thank you in advance

[Fowmy]

@tomwolswijk 

In your sample data, I found duplicate records for each month for the members. How do I know which line to check with, I can summarize but what if the categories are different lines Good and Bad? 

Please clarify and share a sample that repsents your real data possible.

Dec 19	100	Jansen	Good	500,000
Dec 19	100	Jansen	Good	100,000

________________________

If my answer was helpful, please mark this post as a solution, this will also help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube
LinkedIn

[tomwolswijk]

Each client hasmultiple rows, because 1 client has several loans. But for each reporting date, the client has 1 category. Therefore it is not possible that 1 cliet has Good and Bad in the same reporting month.

In Excel i would have used something like VLOOKUP to search for the Category value of previous month for each client. Then index it (1 to 5) and compare is. Something like this:

R

Reporting Date	Client ID	Client Name	Category (reporting date)	Amount	Category (previous date)	Index Category (Reporting Date)	Index Category (Previous date)	Change in category
Dec 19	100	Jansen	Good	500,000	Bad	5	1	4
Dec 19	100	Jansen	Good	100,000	Bad	5	1	4

And then use 'Change in Category' to determine if a client is downgrades (-) or upgraded (+).

Could you help me in making the 'Category (previous date)' kolom?

But dont know how to do this in PowerBI. 

Thanks in advance!

[amitchandak]

@tomwolswijk , if you have date, or you can create date using month nad year. use time intelligence with date table

Date = "01 " +[Month] //change data type as date

Create a measure

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

Using diff you can create a good or bad measures.

or create a column like for last month amount

sumx(filter(table, [Client ID] =earlier([Client ID]) && eomonth([Date],0) = eomonth(arlier([Date]),-1)),[Amount])

not you can create good or bad based on that