Calculating % change from the previous year

[CEdward]

Greetings,

I have a dataset on expenses and am trying to calculate three different, but similar percentages:

1. % change in total expenses, year-to-date (relative to last year's total given the same number of days)
2. % change in total expenses for the current month (relative to last year's total for that same month)
3. % change in total expenses for the current quarter (relative to last year's total for that same month)

This is a bit tricky because I need to somehow match the time periods to ensure a fair comparison (e.g. it would be wrong to compare the total expenses for the month of March this year wherein only half the month has passed to all of the expenses for March of last year).

For example, in the dataset that I included, March expenses for the current year are only incurred on two days (March 2nd and 5th). Assuming that March 5th is the current day and I want to calculate the % change in total expenses, YTD, I would compare the total expenses up to the 5th for the current year relative to the total expenses up to the 5th for 2022.

Ideally, the calculation of each of these reflects how I use my slicer (e.g. so if I select 2023, then comparisons are made to 2022; 2022 to 2021, etc.)

[alansidman]

If the following presentation is acceptable to allow further analysis then Power Query may be the solution.

Excel 2016 (Windows) 64 bit

	A	B	C	D	E	F	G	H	I	J
1	Year	Total		Year	Month	Sum		Year	Quarter	Sum
2	2022	2271.47		2022	January	782.58		2022	Q1	2271.47
3	2023	1145.91		2022	February	874.31		2023	Q1	1145.91
4				2022	March	614.58
5				2023	January	783.64
6				2023	February	339.41
7				2023	March	22.86

Sheet: Table1

[kvsrinivasamurthy]

Pl see file . T2 and U2 are not giving values because September, current month, data not available. For verification change the month manually in Q2 and check.
Try.
In S2

Please Login or Register  to view this content.

In T2

Please Login or Register  to view this content.

In U2

Please Login or Register  to view this content.

[CEdward]

If the following presentation is acceptable to allow further analysis then Power Query may be the solution.

Excel 2016 (Windows) 64 bit

	A	B	C	D	E	F	G	H	I	J
1	Year	Total		Year	Month	Sum		Year	Quarter	Sum
2	2022	2271.47		2022	January	782.58		2022	Q1	2271.47
3	2023	1145.91		2022	February	874.31		2023	Q1	1145.91
4				2022	March	614.58
5				2023	January	783.64
6				2023	February	339.41
7				2023	March	22.86

Sheet: Table1

Thank you for this response, Alan. I will have to explore Power Query more to better understand your solution.

[CEdward]

Pl see file . T2 and U2 are not giving values because September, current month, data not available. For verification change the month manually in Q2 and check.
Try.
In S2

Please Login or Register  to view this content.

In T2

Please Login or Register  to view this content.

In U2

Please Login or Register  to view this content.

Thank you, kvsrinivasamurthy. I think I understand the structure of your solution, but it is not quite yielding the right answer and I do not know why.

A couple of things.

First, I think the numerator and denominator are mixed up. The typical formula is (V2 - V1)/V1 or equivalently (V2/V1) - 1 I believe.

Still, when I calculate the sums manually to determine the % change, it doesn't equal what the formula gives and I don't know why. For example, suppose I set the month to March. The total expenses for this year up until March 5th is approximately 1,145 and for lasts year it is 1,820. This gives a % difference of -37%, but your formula shows -50.13%.

[kvsrinivasamurthy]

Revised formulas
In S2

Please Login or Register  to view this content.

In T2

Please Login or Register  to view this content.

In U2

Please Login or Register  to view this content.