Is it possible to calculate change from previous column in a matrix

[ANP]

I have data source like below

Month-Year	Team	Value
Jul-2023	A	1.2
Aug-2023	A	1.4
Jul-2023	B	2.2
Aug-2023	B	2.1
and so on

output I want

Team	Jul-23	Aug-23	Diff
A	1.2	1.4	0.2
B	2.2	2.1	-0.1

How I can calculate Diff, I dont have and dont need any date table so no point of mentioning time intelligence solution. How can I calculate Diff Column. I tried having index on rows like below

Month-Year	Team	Value	Index
Jul-2023	A	1.2	1
Aug-2023	A	1.4	2
Jul-2023	B	2.2	1
Aug-2023	B	2.1	2
and so on

and the measure like Diff = Table[Value] - Average(Table[Value]{Index-1}) but it gives some cyclic reference issue as index is getting repeated and team is not referenced in formula I guess. Please help

[ryan_mayu · Accepted solution]

@ANP 

it's better to have a date table to use  time intelligence solution.

if not, you can change the first column to date type

then create a column

Column = 
VAR _LAST=MAXX(FILTER('Table','Table'[Team]=EARLIER('Table'[Team])&&'Table'[Month-Year]=EDATE(EARLIER('Table'[Month-Year]),-1)),'Table'[Value])
RETURN IF(ISBLANK(_LAST),BLANK(),'Table'[Value]-_LAST)

[ryan_mayu]

will your request change in the future? currenly, the diff is between jul and aug. will the calculation of diff change in the future?

[ANP]

HI, Yes this is just example.. months will keep on increasing in data table when i refresh data source on 1st of every month last month-year will be added with a value hence I will require all differences ( Aug-Jul, Sep-Aug, Nov-Oct and so forth) .. there are around 10-12 teams for this data would be added for everymonth.

[ryan_mayu · Accepted solution]

@ANP 

it's better to have a date table to use  time intelligence solution.

if not, you can change the first column to date type

then create a column

Column = 
VAR _LAST=MAXX(FILTER('Table','Table'[Team]=EARLIER('Table'[Team])&&'Table'[Month-Year]=EDATE(EARLIER('Table'[Month-Year]),-1)),'Table'[Value])
RETURN IF(ISBLANK(_LAST),BLANK(),'Table'[Value]-_LAST)

[ANP]

Hmm.. looks like thats the only way I had to add a date table... Many thanks Ryan.. however I have one more query if you can help.. month on month is working fine..  if I have to show a column of improvement values as 'Y' and "N" in the end..  lets say if any of the value Aug-Jul, Sep-Aug, Oct-Sep, Nov-Oct is coming as negative then final improvement column will have value N else Y.....

[ryan_mayu]

all the difference is negavie then shows "n", else show "Y"?

[ANP]

for Y all the differences has to be positive .. if any one is negative then 'N'.. Also It should work with the months filter ( lets say I do it for Q2 then only Apr, May , June months will be shown and the calculation above should only done for these months )

[ryan_mayu · Accepted solution]

@ANP 

pls see this

Measure = 
VAR _count=CALCULATE(count('Table'[Column]),ALLSELECTED('Table'[Month-Year]),'Table'[Column]<0)
return if(ISBLANK(_count),"y","n")

[ANP]

wow.. that was simple.. Thanks Ryan..🙏 .. ..  I am showing the final output in 'Matrix' type chart ...by this way I was able to show mom improvement (the improvement measure you created I added as value in matrix chart hence showing for each month as that is the column) but cluless how can I show 'Always improving' as separate column in 'matrix' chart or do you suggest any other chart type( last column in below image ).. lets say there are three months I selected in filter, in that if all the month improvements are Y then Y else  N .. sorry if I am being too demanding ..

[ryan_mayu]

sry ,i don't know how to display like this. it's a matrx + table. Maybe let's see if anyone else can help you on this.

[ANP]

np.. Ryan thanks a lot for your help

[ryan_mayu]

you are welcome