Change Calculation (Yearly & Monthly)

[melnaggar]

Hi everyone, 

I have a data set similar to the below table.  I want to calculate the Monthly and annual change between 2020-2019  2020-2018. 

Appreciate your support 

Cheers!

Year	Collection Date	Month	Product A	Product B	Product C
2018	1/31/2018	1	23	75	3.3
2018	2/28/2018	2	21	69	3.3
2018	3/31/2018	3	24	78	3.3
2018	4/30/2018	4	21	68	3.2
2018	5/31/2018	5	19	63	3.2
2018	6/30/2018	6	16	49	3.1
2018	7/31/2018	7	21	61	2.9
2018	8/31/2018	8	22	64	2.9
2018	9/30/2018	9	20	59	3.0
2018	10/30/2018	10	21	66	3.2
2018	11/30/2018	11	23	72	3.2
2018	12/31/2018	12	25	80	3.2
2019	1/31/2019	1	24	77	3.2
2019	2/28/2019	2	22	71	3.2
2019	3/31/2019	3	24	79	3.3
2019	4/30/2019	4	24	79	3.2
2019	5/31/2019	5	15	49	3.3
2019	6/30/2019	6	22	61	2.8
2019	7/31/2019	7	22	65	2.9
2019	8/31/2019	8	23	68	2.9
2019	9/30/2019	9	21	64	3.0
2019	10/30/2019	10	23	73	3.2
2019	11/30/2019	11	24	78	3.3
2019	12/31/2019	12	27	85	3.2
2020	1/31/2020	1	27	87	3.3
2020	2/28/2020	2	22	71	3.3
2020	3/31/2020	3	11	41	3.9
2020	4/30/2020	4	3	20	6.9
2020	5/31/2020	5	4	23	5.2
2020	6/30/2020	6	6	23	4.1
2020	7/31/2020	7	10	29	3.1
2020	8/31/2020	8	14	40	2.9
2020	9/30/2020	9	11	41	3.8
2020	10/30/2020	10	13	50	3.8
2020	11/30/2020	11	12	50	4.2
2020	12/31/2020	12	17	67	3.9

@amitchandak 

[v-yalanwu-msft · Accepted solution]

Hi, @melnaggar ;

You could unpivot it or create a new table as follows:

Then create a measure to calculate a year change.

Charges = 
VAR _lastyeara=CALCULATE(SUM(Data[Product A]),FILTER(ALLSELECTED(Data),[Year ]=MAX([Year ])-1))
VAR _minyeara=CALCULATE(SUM(Data[Product A]),FILTER(ALLSELECTED(Data),[Year ]=MINX(ALL(Data),[Year ])))
VAR _maxyeara=CALCULATE(SUM(Data[Product A]),FILTER(ALLSELECTED(Data),[Year ]=MAXX(ALL(Data),[Year ])))
VAR _lastyearB=CALCULATE(SUM(Data[Product B]),FILTER(ALLSELECTED(Data),[Year ]=MAX([Year ])-1))
VAR _minyearB=CALCULATE(SUM(Data[Product B]),FILTER(ALLSELECTED(Data),[Year ]=MINX(ALL(Data),[Year ])))
VAR _maxyearB=CALCULATE(SUM(Data[Product B]),FILTER(ALLSELECTED(Data),[Year ]=MAXX(ALL(Data),[Year ])))
VAR _lastyearC=CALCULATE(SUM(Data[Product C]),FILTER(ALLSELECTED(Data),[Year ]=MAX([Year ])-1))
VAR _minyearC=CALCULATE(SUM(Data[Product C]),FILTER(ALLSELECTED(Data),[Year ]=MINX(ALL(Data),[Year ])))
VAR _maxyearC=CALCULATE(SUM(Data[Product C]),FILTER(ALLSELECTED(Data),[Year ]=MAXX(ALL(Data),[Year ])))
return 
 SWITCH(MAX('Table'[cate]),"Product A",IF(MAX([Year ])=MINX(ALL(Data),[Year ]),1,IF(ISFILTERED('Data'[Year ]),DIVIDE( SUM([Product A])- _lastyeara,_lastyeara),DIVIDE( _maxyeara-_minyeara,_minyeara))),
 "Product B",IF(MAX([Year ])=MINX(ALL(Data),[Year ]),1,IF(ISFILTERED('Data'[Year ]),DIVIDE( SUM([Product B])- _lastyearB,_lastyearB),DIVIDE( _maxyearB-_minyearB,_minyearB))),
 "Product C",IF(MAX([Year ])=MINX(ALL(Data),[Year ]),1,IF(ISFILTERED('Data'[Year ]),DIVIDE( SUM([Product C])- _lastyearC,_lastyearC),DIVIDE( _maxyearC-_minyearC,_minyearC))))

The final output is shown below:

It makes it easier to give you a solution  if you could share the results you want to output, and screenshots would be fine.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[melnaggar]

@amitchandak  Thank you so much it was very helpful.

Is there any other way to calculate the change as a percentage (YTD, MTD) without merging products columns to one column? 

[amitchandak]

@melnaggar , Check if attached file can help