Case Statement & Adjust year function errors (for fiscal year selection from a date field)

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I'm trying to create a case statement that returns the year "YYYY" for a date field (for fiscal year reporting). The following statement is what I've written, and the only part that doesn't work is months 1-4 which all use the adjust year function.

Case(Month([Est Start Date]), 1, (AdjustYear([Est Start Date],-1)), 2, (AdjustYear([Est Start Date],-1)), 3, (AdjustYear([Est Start Date],-1)), 4, (AdjustYear([Est Start Date],-1)), 5, (Year([Est Start Date])), 6, (Year([Est Start Date])), 7, (Year([Est Start Date])), 8, (Year([Est Start Date])), 9, (Year([Est Start Date])), 10, (Year([Est Start Date])), 11, (Year([Est Start Date])), 12, (Year([Est Start Date])), null)

Any help would be great!

Photo of Ryan

Ryan

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Photo of Ryan

Ryan

  • 0 Points
Found the solution--avoided the Adjust year. Below is the completed formula (for a fiscal year starting in May ending in April):



Case(Month([Est Start Date]), 1, (Year([Est Start Date])), 2, (Year([Est Start Date])), 3, (Year([Est Start Date])), 4, (Year([Est Start Date])), 5, (Year([Est Start Date])+1), 6, (Year([Est Start Date])+1), 7, (Year([Est Start Date])+1), 8, (Year([Est Start Date])+1), 9, (Year([Est Start Date])+1), 10, (Year([Est Start Date])+1), 11, (Year([Est Start Date])+1), 12, (Year([Est Start Date])+1), null)