Home Forums Power Pivot Adding Fiscal Year and Fiscal Quarter to Date table

This topic contains 5 replies, has 3 voices, and was last updated by  Jerid421 7 years, 10 months ago.

Viewing 6 posts - 1 through 6 (of 6 total)
• Author
Posts
• #6435

bdeuce14
Participant
• Started: 11
• Replies: 14
• Total: 25

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!

#6438

tomallan
Keymaster
• Started: 0
• Replies: 417
• Total: 417

What format do you want Fiscal Year and Fiscal Quarter to have?

#6439

bdeuce14
Participant
• Started: 11
• Replies: 14
• Total: 25

Hi Tom,

I’d like the fiscal year format to read “FY – 16” and fiscal quarter to read “Q3.”

#6440

tomallan
Keymaster
• Started: 0
• Replies: 417
• Total: 417

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 )`

#6443

Jerid421
Participant
• Started: 1
• Replies: 9
• Total: 10

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.  : )

#6444

Jerid421
Participant
• Started: 1
• Replies: 9
• Total: 10

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.  : (

Viewing 6 posts - 1 through 6 (of 6 total)

The forum ‘Power Pivot’ is closed to new topics and replies.