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