This topic contains 5 replies, has 3 voices, and was last updated by 
-
Posts
-
I’m trying to columns for Fiscal Year and Fiscal Quarter to my date table.
Date table start date is 10/1/2014
Date table end date is 9/30/2016
Our fiscal year starts in October so fiscal year 2015 is 10/1/2014 – 9/30/2015 and fiscal year 2016 is 10/1/2015 – 9/30/2016.
I’ve tried different formulas but it’s still not working.
In my date table I have columns for Date, Year, Month Number, Month Name.
Any help is appreciated!
What format do you want Fiscal Year and Fiscal Quarter to have?
Hi Tom,
I’d like the fiscal year format to read “FY – 16” and fiscal quarter to read “Q3.”
For calculated columns, you could try:
Fiscal Quarter
= "Q" & FORMAT ( [Date], "Q" ) + IF ( MONTH ( [Date] ) >= 10, -3, 1 )Fiscal Year
= "FY - " & YEAR ( [Date] ) + IF ( MONTH ( [Date] ) >= 10, 1, 0 )I did it this way:
I added two calculated columns, one called Fiscal_Year and one called Fiscal_Quarter. Because your dates are in perfect alignment with the regular quarters (some companies can have first quarter start mid-month), I just shifted everything back one quarter.
Fiscal_Year: =IF([quarter_text)=”Q4″, [year_no]-1, [year_no])
Fiscal_Quarter: =IF([quarter_text]=”Q1″,”Q4″,”Q”&INT(Right([quarter_text],1)-1))
But I’m sure Tom Allen’s answer is correct too, as he is a wizard and is usually right. : )
Sorry… the first equation should be:
=IF([quarter_text)=”Q4″, [year_no]+1, [year_no])
not minus one. You’re basically shoving every years fourth quarter into the future year.
And the second equations should be:
=IF([quarter_text]=”Q4″,”Q1″,”Q”&INT(Right([quarter_text],1)+1)) which switches Q1 and Q4 and replaces the minus with a plus.
Sorry, I did these quickly without checking my work. : (
The forum ‘Power Pivot’ is closed to new topics and replies.