Looking for second most recent performance year

Hi Anh,

I started with a dashboard filter on Season Fiscal Current Year Offset = 0 to limit the dashboard to constituent with tickets to the current season. Then for results by constituent I have a Pivot with Constituent ID and a value formula like this:

( [Max Season Fiscal Year], [Season Fiscal Current Year Offset < 0] )

To then pivot that into a Bar chart, we could create a value per FY using Multi-Pass Aggregation to do a per Constituent evaluation of their most recent FY prior to the current one. The formula for constituents whose most recent prior was 2021 would look like this:

SUM([Constituent ID],
   IF( ( [Max Season Fiscal Year] , [Season Fiscal Current Year Offset < 0] ) = 2021
      ,1 ,NULL
   )
 )

And if you'd like to get fancy, it is possible to produce a single formula over Season Fiscal Year on Categories that while is more dense to parse, can be rendered over any number of Season Fiscal Year with only a single value rather than a value per Season Fiscal Year.

SUM([Constituent ID],
   IF(
      ( [Max Season Fiscal Year], [Season Fiscal Current Year Offset = 0], ALL([Season Fiscal Year]) ) > 0
      AND
      ( [Max Season Fiscal Year], [Season Fiscal Current Year Offset < 0], ALL([Season Fiscal Year]) )
         = [Max Season Fiscal Year]
      ,1 , NULL
   )
 )

If the constituent is a patron in the current season... AND their most recent season prior to the current season matches the season of the category/bar in the widget, then count this constituent in that category/bar.

Showing my work: MostRecentYear.dash

Best,
Chris

Hey Chris, I have another quick question coming back to this dashboard.  If I add additional sub-filters like performance detail type(screenshot below) or price category, the dashboard would go ahead and filter all current fiscal year constituents that met that criteria?  I'm thinking in my screenshot below the dashboard would go ahead and filter the current fiscal year's patrons that were in the perf type = community concert.  

Hey Chris, I have another quick question coming back to this dashboard.  If I add additional sub-filters like performance detail type(screenshot below) or price category, the dashboard would go ahead and filter all current fiscal year constituents that met that criteria?  I'm thinking in my screenshot below the dashboard would go ahead and filter the current fiscal year's patrons that were in the perf type = community concert.  

Hi Michael,

Yes, filtering on type would limit then the scope to constituents attending that type and when they last attended that same type.

Thanks Chris. This is helpful. Initially I was thinking this would filter/ limit only the current fiscal year's perf type.

If I didn't care what their previous performance was, like they went to a community concert type this fiscal year, but any concert type in the previous fiscal years, what would be the best way to go about that? I'm thinking maybe a modification to the formula so that it overrides the concert type when looking at previous fiscal years?

Thanks again,

Mike

Hi Michael,

You can do that by adding an ALL() function to override the performance type from the filter. E.g.

( [Max Season Fiscal Year] , [Season Fiscal Current Year Offset < 0] , ALL( [Performance Type] ) )

Thanks Chris, 

That makes sense. Grateful for Anh for starting this thread, and you for working through it for us all!