Calculate difference between rows in a single column based on multiple other columns

[appelture]

Hi, 

I have a table of data as follows, where I need to calculate the difference in AverageRating by CC_AppraisalYear, but also by the other columns i.e. calculate the difference in AverageRating for CMMI_PROCESS_AREA, CC_CodeType and CommonFeature between the later year and the earlier year (the CC_AppraisalYear). The "YearDiff" column shows my manual workings of how the calc should operate:

CC_AppraisalYear	CMMI_PROCESS_AREA	CC_CodeType	CommonFeature	AverageRating	YearDiff
2022	Enterprise Architecture Plan	Generic - Goal	C2P	3.2	1.8
2022	Enterprise Architecture Plan	Generic - Goal	A2P	2.6	2.4
2022	Enterprise Architecture Plan	Generic - Goal	AP	1.8	3.2
2022	Enterprise Architecture Plan	Generic - Goal	M&A	1.3	1.7
2022	Enterprise Architecture Plan	Generic - Goal	VI	0.2	0
2022	Enterprise Architecture Management	Generic - Goal	C2P	5	0
2022	Enterprise Architecture Management	Generic - Goal	A2P	3.8	-1.2
2022	Enterprise Architecture Management	Generic - Goal	AP	1.2	0.6
2022	Enterprise Architecture Management	Generic - Goal	M&A	0.2	0
2022	Enterprise Architecture Management	Generic - Goal	VI	0	0
2022	Enterprise Architecture Plan	Specific - Level	C2P	0	0
2022	Enterprise Architecture Plan	Specific - Level	A2P	0	0
2022	Enterprise Architecture Plan	Specific - Level	AP	0	0
2022	Enterprise Architecture Plan	Specific - Level	M&A	0	0
2022	Enterprise Architecture Plan	Specific - Level	VI	0	0
2022	Enterprise Architecture Management	Specific - Level	C2P	0	0
2022	Enterprise Architecture Management	Specific - Level	A2P	0	0
2022	Enterprise Architecture Management	Specific - Level	AP	0	0
2022	Enterprise Architecture Management	Specific - Level	M&A	0	0
2022	Enterprise Architecture Management	Specific - Level	VI	0	0
2023	Enterprise Architecture Plan	Generic - Goal	C2P	5	null
2023	Enterprise Architecture Plan	Generic - Goal	A2P	5	null
2023	Enterprise Architecture Plan	Generic - Goal	AP	5	null
2023	Enterprise Architecture Plan	Generic - Goal	M&A	3	null
2023	Enterprise Architecture Plan	Generic - Goal	VI	0.2	null
2023	Enterprise Architecture Management	Generic - Goal	C2P	5	null
2023	Enterprise Architecture Management	Generic - Goal	A2P	2.6	null
2023	Enterprise Architecture Management	Generic - Goal	AP	1.8	null
2023	Enterprise Architecture Management	Generic - Goal	M&A	0.2	null
2023	Enterprise Architecture Management	Generic - Goal	VI	0	null
2023	Enterprise Architecture Plan	Specific - Level	C2P	0	null
2023	Enterprise Architecture Plan	Specific - Level	A2P	0	null
2023	Enterprise Architecture Plan	Specific - Level	AP	0	null
2023	Enterprise Architecture Plan	Specific - Level	M&A	0	null
2023	Enterprise Architecture Plan	Specific - Level	VI	0	null
2023	Enterprise Architecture Management	Specific - Level	C2P	0	null
2023	Enterprise Architecture Management	Specific - Level	A2P	0	null
2023	Enterprise Architecture Management	Specific - Level	AP	0	null
2023	Enterprise Architecture Management	Specific - Level	M&A	0	null
2023	Enterprise Architecture Management	Specific - Level	VI	0	null

So, for 2002, Enterprise Architecture Plan, Generic - Goal, C2P the YearDiff is 5 - 3.2 = 1.8 . The YearDiff for exactly the same row from 2023 is null as we don't yet have data from 2024.

I would love to be able to put in some of my attempts but they have all failed spectacularly! I am on the verge of splitting the table by CC_AppraisalYear and then doing a Merge on CMMI_PROCESS_AREA, CC_CodeType and CommonFeature to get data into a single row, from where I could calculate the difference between the AverageRating columns?!

Any help would be so appreciated - and whether PowerQuery or DAX would be the better option? Could the calculation be dyamic if I add additional years into CC_AppraisalYear and then use a filter to do the calcation on page? Thanks so much 🙂

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

Yeardiff = if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[CC_AppraisalYear]=EARLIER(Data[CC_AppraisalYear])+1))=0,BLANK(),CALCULATE(MIN(Data[AverageRating]),FILTER(Data,Data[CMMI_PROCESS_AREA]=EARLIER(Data[CMMI_PROCESS_AREA])&&Data[CC_CodeType]=EARLIER(Data[CC_CodeType])&&Data[CommonFeature]=EARLIER(Data[CommonFeature])&&Data[CC_AppraisalYear]=EARLIER(Data[CC_AppraisalYear])+1))-Data[AverageRating])

Hope this helps.

[appelture]

Thank you so much, @Ashish_Mathur 🙂

[Ashish_Mathur]

You are welcome.

[appelture]

Hi again @Ashish_Mathur, I hope you might be able to provide some additional help on this one. Everything is working perfectly - I can see the yeardiff against the 'previous' years row. Is there a way to reflect this in the opposite? So, to see the yeardiff against the later year instead? i.e. yeardiff against a row for 2023 instead of 2022 (showing the increase/decrease of AverageRating exactly as before)? Any additional assistance would be so appreciated 🙂

[Ashish_Mathur]

Hi,

What result do you get when you change +1 in the formula to -1.  If it does not help, then share the data and show the expected result very clearly.

[Ashish_Mathur]

Hi,

Based on the data that you have shared, show the expected result.

[appelture]

Thanks - I have amended my post, above 🙂

[danextian]

Hi @appelture ,

What do you mean by calculate the difference in AverageRating? Using your data, please provide a sample formula of how that would be calculated. For example, sum of average rating for CMMI_PROCESS_AREA for 2022 vs 2023. Also, please provide  a data that includes both years.

[appelture]

Thanks - I have amended my post, above 🙂