You'll just have to do some string calculations (using LEFT and RIGHT) to pull the pieces you want. I opted to do it in 3 calculations for the sake of clarity.
First to get the proper information before the dash:
'FY' + RIGHT(LEFT([Fiscal], 6), 2) + '-'
This calculation take the left 6 characters (FY2019) then trims off the right two (19), then adds in FY = FY19.
IF LEN([Fiscal]) = 10 THEN RIGHT([Fiscal], 3)
ELSE 'P0' + RIGHT([Fiscal], 1)
This calculation looks at the total length of the value. If it is 10, then that means it is P10, P11, or P12 and we will just take those 3 characters from the entire string. If it is less than that, we will take just the right single character and add P0 to it. The result is P01, P02...P10, P11, etc.
Then we will combine them:
[@Year] + [@Month]
And that should do it!
Attached is a workbook containing the calcs. Let me know if this works for you.
Fiscal Year_v2019.2.twbx 32.3 KB
Thank you for your input, sir. I appreciate your help.
Thank you for your detailed explanation of the solution. You are awesome.
It's a pleasure, happy to help - and such a great explanation from Kevin!
Main thing is you've got what you needed
Yeah, but using MID makes much more sense! Nice work, Nik.