New Metric :Fiscal Year

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!!

Date Time

Best Practices

Batch Macro

Documentation

Accepted answers

MichalM

@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

fy.yxmd

All comments

MichalM

@chughes49

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

fy.yxmd

LordNeilLord

Hey @chughes49

How about this

FY.yxmd

LordNeilLord

@MichalM Almost the exact same solution!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703