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
HI Chis - I just wanted to say I downloaded this dash and it is very insightful. I can also see filtering some of our production seasons within our fiscal year to look at behavior trends of returning productions we offer as well. I may follow up with you separately regarding this.
Thanks
Mike