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!
Madeline's formula is taking the contribution amount within a specified fiscal year, and the limiting that to contributions over 1 year ago. The 1825 to go back 5 years from 1 year ago is just to create a big enough contribution date window to let the campaign fiscal year filter handle the "how far beyond 1 year ago" for included contributions.
Also, a formula like ( SUM ( [Amount] ) , ALL ( [Campaign] ) , ALL ( [Campaign Fiscal Year] , ALL ( [Whatever] ) , ... )
Will return the total contribution amount for the whole cube if you have enough ALL functions and fields to override all the pivot table rows and columns fields and the widget and dashboard filters.
If I'm not filtering on campaign year at all, just contribution dates, is there a different formula I should use? I can't just use the PASTYEAR function because it doesn't work well with the dashboard contribution date filter set to 7/1/19 through most recent. It seems to only like when I give the contribution date filter an end date parameter, which I'm trying to avoid so I can have an auto-updating dashboard that always shows fiscal year to date for this FY and last FY.
And, for another dashboard, if I want to add a value column to my pivot table for Total Contribution Amount in last 10 (or 20, or all) years, without having to set all the other date filters at the value level (so that the rest of the widget can just inherit the dashboard filters), how many ALLs do I need to include? I tried (SUM([Amount]), ALL([days in date])), but that seems to only give me the total contribution amount over all time for funds that also have a contribution that fits the other date parameters in my pivot table/dashboard date filter. I want to also see the funds that got a contribution in the past 10, 20, or all years. I've attached a .dash file that illustrates the issue I'm running into. The first widget works but requires I update a bunch of value filters each month to pull the report. The bottom widget (with working in the title) doesn't include the same number of rows as the top one but relies on updating only one dashboard date filter each month.4857.FOLG-AllFundsWidgets-LGWorking.dash
Do you not use campaigns to differentiate fiscal years at Folger? Is the only way you differentiate fiscal years by contribution date?
We use fiscal years for some campaigns (annual fund, membership, major grants for programs), but not for others (endowment, government grants), so to pull a complete picture of our fundraising for our fiscal year, we have to pull on contribution date. This also means that goals in Tessitura aren't super useful to us, since setting a goal for our multi-decade endowment campaign would be impossible. And we also count our fundraising efforts based on when we get the contribution not when the contribution is intended for, so if we get a theatre sponsorship gift for next season early, it goes into the next FY's campaign but we want to count the contribution now, hence the reliance on contribution dates.