Calculating Percent Diff Year / Month

Hi all,

I’ve been scouring this community for straightforward advice but couldn’t find any. I have this pivot table but I need to be able to calculate the percentage difference between the previous year’s month and the next year’s month, preferably as a column after each year. Is there any way to do so? My dates are in YYYY-MM-DD and I used another formula to convert those months to the actual month name (FYI). Any thoughts?


Hi Kat,
By far you can’t calculate % difference within the pivot table, but the calculation can be done in the upstream base table. See the gif created by Sigma expert, @Yee, below:

1 Like

Thanks Cathy! Do you know what the formula was for the percent difference in the base table? The formula wasn’t shown in the gif. Greatly appreciated though :slight_smile: I used:

[Sum of Metered Kwh] - Lead([Sum of Metered Kwh]) / ([Sum of Metered Kwh] + Lead([Sum of Metered Kwh]) / 2)

but it is showing up blank:

There are two columns that I made.

First is the [Lag of Revenue] column which would be: Lag([Revenue], 1) which offsets the revenue column.

Second I did ([Revenue]/[Lag of Revenue])-1 in my example to calculate the percent difference, since I’m now comparing the months.

There are certainly other ways to accomplish the comparison, but most use Lag() or Lead(). I know some people prefer to not group by year and only group by month, and then use a Lag([column], 12)to frameshift the year. I like my approach since I can see more easily that my lag is doing what I’d expect.

Hope this helps!

Thank you so much Yee! Super helpful, have a great one. :slight_smile: