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?
Kat
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:
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 
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.
