Hello! Is there a way to show last fiscal year's contribution total in a pivot table that is showing contributions for this fiscal year-to-date compared to last fiscal year-to-date? I'm doing the this year to last year comparison with the PASTYEAR function, but I'm not sure how to compare this year's current totals to last year's year-end totals.
(Bonus if I can also throw in a comparison to the last 20 years' total so that I can get all my zero rows to show up in the pivot table!)
Leslie Gehring
Hi Leslie, Does the At-A-GlanceContributions (Chris W special) help?
https://community.tessituranetwork.com/tessitura_software_forums/f/tessitura_shared_reports-9/21995/date-and-time----analytics-functions 5417.At-a-GlanceContributions.dash
I think the problem is that I'm trying to compare contribution amounts over different-length periods of time in the same pivot table. I want to see FY20 YTD, FY19 YTD, and FY19 year-end totals, all in the same pivot table, but I can't figure out how to make that work with the date functions. (And, yes, I'd also like to see FY2000-FY2019 contribution totals as a dummy column to force all the zero-dollar rows to appear.)
You can accomplish year to date using Days in Date with an advanced filter and change the numbers if you're looking at years prior to LY. Then for the other years, you would remove the Days in Date part and just select the campaign fy (or campaign) to show the total. You would do most of your filtering within each column instead of on the widget or dashboard level.
Aha! Brilliant! I can't use campaign FYs since some of our funds (like endowments) live in non-FY campaigns, but this is workable. I wish I could just exclude particular columns from widget filters (the way I can exclude widget from dashboard filters), but this'll do. Thank you so much!
And just in time for my 10am meeting about how to fix our reports! :)
Madeline, this might be a dumb question, but what does the 1825 represent in this formula? 366 seems to indicate one year ago, but I can't figure out the 1825. It works for my report, but I don't understand why.
Leslie, I'm going to tag Chris Wallingford into this for a real answer because I stole it from him. I divided 1825 by 365 and it comes out to 5 so I think it allows you to go back up to 5 years? Then if you wanted to include another column with the year prior you would do 731 as your offset. All I know is it works for us too!