Contribution comparison to past years

Leslie - see if this dashboard helps? 6318.20DEVOMonthlyReport.dash The pivot table has some the year to date comparison formulas.

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

Madeline, that has a lot of the same columns I'm looking for, but I'm trying to add a column for FY19 contribution total in addition to FY19 year-to-date. That way I can see where we are compared to where we were this time last year and where we are this year compared to the total we raised in all of last year. Does that make sense?

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

Yes - that does make sense. You could use the formula in that dashboard where I figure out the difference but remove the advanced date filters and achieve what you're looking for. My analytics is down right now so I can't double-check my work but something along the lines of (Contribution Total, CampaignFY19) - (Contribution Total, Campaign20) with no advanced filters.

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.