Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Fiscal Year Column from Date

Hi,

 

I'm looking to create a fiscal year column from a date entry but am having difficulty finding a current solution that matches my goal. For example, I'm looking for "FY21, 1st Qtr" from the date 7/1/2020 12:52:00 AM. The fiscal year begins July 1st.

 

Thanks in advance for your help!

Need Help
  • d_gosbell's avatar
    d_gosbell
    5 years ago

    You could do this with a calculated column like the following

    Fiscal Qtr = 
var year = YEAR('Date'[Date])
var month = MONTH('Date'[Date])
var quarter = QUARTER('Date'[Date])
var fiscal_year = if (month >= 7, year + 1, year)
var fiscal_year_name = "FY" & mod(fiscal_year, 100)
var fiscal_quarter = if(quarter > 2, quarter - 2, quarter + 2)
var fiscal_quarter_name = SWITCH(fiscal_quarter, 1, "1st Qtr",2,"2nd Qtr",3, "3rd Qtr", 4, "4th Qtr")
return fiscal_year_name & " " & fiscal_quarter_name

2 Replies

  • d_gosbell's avatar
    d_gosbell
    Super User
    5 years ago

    You could do this with a calculated column like the following

    Fiscal Qtr = 
var year = YEAR('Date'[Date])
var month = MONTH('Date'[Date])
var quarter = QUARTER('Date'[Date])
var fiscal_year = if (month >= 7, year + 1, year)
var fiscal_year_name = "FY" & mod(fiscal_year, 100)
var fiscal_quarter = if(quarter > 2, quarter - 2, quarter + 2)
var fiscal_quarter_name = SWITCH(fiscal_quarter, 1, "1st Qtr",2,"2nd Qtr",3, "3rd Qtr", 4, "4th Qtr")
return fiscal_year_name & " " & fiscal_quarter_name