New Metric :Fiscal Year

[chughes49]

Hey all,

Currently I have alot of historical data that I built a workflow around. I built a batch macro to pull in historical data and append it to one excel file. I get a new file each month. I have three date metrics already (Ex: Month=Jan, Year=2019, Month&Year= Jan 2019). I need to create a fourth metric that is Fiscal Year (ex: FY18, FY19). What kind of formula could I create where it would be ongoing so FY17, FY18, FY19, etc..? Each Fiscal year runs like this FY18= July 2018-June 2019; FY19= July 2019-June 2020. Any help would be greatly appreciated thanks!!

[MichalM · Accepted solution]

@chughes49 

First, I'd turn the Monh&Year into an actual date using DateTime Parse. Once I have the date, I can extract Month number from it using DateTimeFormat formula and use the number to build out a conditional statement. Example attached.

IF ToNumber(DateTimeFormat([DateTime_Out], "%m")) < 7 THEN "FY"+Right(ToString([Year]-1),2)
ELSE "FY"+Right(ToString([Year]),2)
ENDIF

[LordNeilLord]

Hey @chughes49 

How about this

[LordNeilLord]

@MichalM Almost the exact same solution!

[MichalM]

Great minds think alike @LordNeilLord ?

[chughes49]

@MichalM @LordNeilLord , 

Thanks guys so much, I forgot to mention the data that I'm using comes over as (Ex: "2019-01-31"). I used the Datetime tool to make sure it was formatted correctly but than when I inputted the formula it gave me this. Any thoughts? I also created Year and Month with these formulas pulled from the original "2019-01-31", Would that be as to why I am getting the error " Formula: invalid type in subtraction operator"?

[MichalM]

You have the Year value generated as a string and you're trying to subtract from it in the formula. You can either output the variable as a number or wrap it in tonumber() formula in the IF statement.