Skip to main content
Sign In |
  Help (new window)

PowerPivotPro's FAQ Site

Go Search
  
PowerPivotPro's FAQ Site > The Great PowerPivot FAQ > I have a date field in my sales table. How can I use the field to group by fiscal year and quarter in my PivotTable?  

The Great PowerPivot FAQ: I have a date field in my sales table. How can I use the field to group by fiscal year and quarter in my PivotTable?

Title

 I have a date field in my sales table. How can I use the field to group by fiscal year and quarter in my PivotTable?  

Answer
You can create calculated fields for fiscal year and quarter in the table after you import the table into PowerPivot. Let's say the name of your table is SalesData and your date field is OrderDate. Let's further assume that your fiscal year begins in April.
 
The calculated field for fiscal year would be:
 
="FY"&YEAR([OrderDate])+(MONTH([OrderDate])>3)  
 
And for fiscal quarter:
 
="FQ"&CEILING(MONTH(DATE(YEAR([OrderDate]),MONTH([OrderDate])-3,1)),3)/3

Client/Server

 Client 

Topic

 F - Calculations (DAX) 

Purpose

 Information 

Answered?

 Yes 

Contributor

 Colin Banfield 

Título

  

Resposta
 

Frage

 Ich habe ein Datumsfeld in meinen Verkaufsdaten. Wie kann ich dieses Feld benutzen um die Daten nach Jahr/Quartal... in meiner PivotTabelle zu gruppieren? 

Antwort
 

preRTM

  
Attachments
Created at 1/20/2010 11:10 PM  by Colin 
Last modified at 4/23/2010 1:33 PM  by Jochen Juelke