I’m trying to create a simple revenue report that breaks down revenue by year and month. I’d like to add a column that show the percentage the revenue increased or decreased from the previous year in the same month. I can’t figure out a formula to accomplish this. Any help is much appreciated! Here’s a link to a copy of my doc.
Your doc is empty. But — if you need to get the value from another row (i.e. to compare this row’s value to that one), you can just use Filter(), and your formula will look like, e.g.:
thisRow.Revenue / thisTable.Filter(Month = thisRow.Month AND Year = thisRow.Year - 1).Revenue
If you’re storing month and year as a date, you can use .RelativeDate(-12) to get the date that’s 1 year ago, e.g.:
thisRow.Revenue / thisTable.Filter([Start of month date] = thisRow.[Start of month date].RelativeDate(-12)).Revenue
Best practice would be to separate this into two steps: first pull the row from 1 year ago into a separate column (e.g. named as “Compare to”), then in your percentage formula reference that column. It will also be more flexible then if you ever want to e.g. introduce a choice of what you want to compare to and make it more complex.
2 Likes
Sorry for the delayed response @Paul_Danyliuk. But you’re solution worked great. Thanks for your help!