Diagonal Calculation with multiple columns and dates involved

[akshbhala]

Hi, I am trying to solve an interesting and challenging problem. 

mth	1.C	2.A	3.B	4.D	5.E
2021-09	1100	4800
2021-10	2000	3000	4000
2021-11	3000	4000	6000	7000
2021-12	4000	5000	7000	9000	1000

I want to calculate diagonally and show the ouput as below 
Note : I have also given new column names

mth	0-1	1-2	2-3	3-4
2021-10	273%	83.3%
2021-11	200.0%	50.0%	57.1%
2021-12	60.0%	57.1%	66.7%	700.0%

Formula : (row 2, column 2)  3000 /  (row1,column1) 1100 = 273%

[v-shex-msft · Accepted solution]

HI @akshbhala,

You can do unpivot columns on your fields to convert them to attribute and value, then remove the character to only keep number in your attribute fields.

Unpivot columns - Power Query | Microsoft Learn

After these steps, you can create segment table with different ranges and use with raw table field and new table segment to create matrix. Then you can write a measure to look up and calculate value based on current category and segment.

Dynamic segmentation – DAX Patterns
Regards,

Xiaoxin Sheng

[HoangHugo]

Hi,

row number or column number are relative things, you should have a exact definition for thes rows, columns

example: row 2 is current month and row 1 is prevous month, and column 2 is current week, and column 1 is previous week. That possible make DAX function