I want to calculate the highest difference of categories between two month

[Anonymous]

Hello I have a data as below,
Created xx months ago is a calculated column which changes it value dynamically as the months changes , as this is the June month going on therefore 
1 - May , 2 - April
Please note I have created month as 3 ,4,5 as well but i want it for 1 and 2

Category	Total hrs.	Created xx months ago
A	20	1
B	50	1
C	30	1
D	15	1
E	75	1
A	80	2
B	30	2
C	75	2
D	30	2
E	25	2

I want to have a calculated pivot table as below

Category	Created xx months ago - 1	Created xx months ago- 2	Difference
A	20	80	60
B	50	30	20
C	30	75	45
D	15	30	15
E	25	25	0

The difference value should be sorted in decreasing order and then 

Now after the difference in calculated and sorted i want to find the rank 1 , rank2 and rank3 category with the decrease/increase percentage

For example - Rank1 - Category A - percentage = 75%

[Ashish_Mathur]

Hi,

It will be a lot easier to solve this problem if you have a Date column.  If you have that column, then share the revised input data.

[Anonymous]

Hello @Ashish_Mathur , yes it does have a date column

Category	Total hrs.	Created xx months ago	date
A	20	1	5/13/2024
B	50	1	5/10/2024
C	30	1	5/14/2024
D	15	1	5/26/2024
E	75	1	5/3/2024
A	80	2	4/9/2024
B	30	2	4/24/2024
C	75	2	4/16/2024
D	30	2	4/10/2024
E	25	2	4/4/2024

Please note - created xx months ago is the dynamic changing value as this is june month therefore 1 represents may month and 2 represents april month. 
Dax used - 

Created xx Months Ago = DATEDIFF('Test'[Date_time],TODAY(),MONTH)

I want to have a pivot table , and a dax query to calculate rank1, rank2, rank3 categories 

[Anonymous]

Hi @Anonymous,

It sounds like you wanted to calculate based on the calculated column which will be change when data updated.

For this scenario, I'd l like to suggest you use Dax expression to get the max and min value from that field based on current category group and stored in the variables.

Then you can use the current category and variable as condition to find out the value, calculate the difference, and write a new measure based on the current difference value to get the rank.

Regards,

Xiaoxin Sheng

[Anonymous]

Hello, can you suggest a dax query for it please