Set Analysis with Master Calendar

[sungyunley]

Hello! 

I want to use set analysis to sum sales QTD, and YTD. I did some research, and found that most folks can achieve this by using a $ expression with YEAR set to TODAY(), i've also seen folks using MAX(Year Sequence Number). The reason i cannot use YEAR set to TODAY() is because our companies fiscal year begins on Feb 1 vs Jan 1, so if the close date of an oppty landed on Jan 2nd 2020, the fiscal year returned should be FY19 opposed to FY20. I am also unable to use MAX(Year Sequence) because our master cal has dates through 2030, so the max year number would be for 2030 instead of the current year. 

I was thinking i can use the fiscal year sequence number, if i can tie it back to today's date. I'd appreciate any other ideas! 

[GaryGiles · Accepted solution]

The YearStart function allows you to specify a starting month offset.  For a Fiscal Year start of February 1st, try this:

=Year(YearStart(Today(),0,2))

The third parameter, 2, specifies February as the starting month.