Year Formats Changing in GETPIVOTDATA

[Claymationator]

I am consolidating a few different data sources in PowerPivot and have a table that contains our fiscal calendar. I am using that calendar as a lookup for the other tables so that I can display the values appropriately as it relates to our business. The fiscal calendar looks like this one here:

Excel 2012
	A	B	C	D	E	F
1	Date	FiscalYear	FiscalMonth	FiscalQuarter	FiscalWeek	DateLookup
2	1/2/2006	2006	1	1	1	P1 - WK1
3	1/3/2006	2006	1	1	1	P1 - WK1
4	1/4/2006	2006	1	1	1	P1 - WK1
5	1/5/2006	2006	1	1	1	P1 - WK1
6	1/6/2006	2006	1	1	1	P1 - WK1
7	1/7/2006	2006	1	1	1	P1 - WK1
8	1/8/2006	2006	1	1	1	P1 - WK1
9	1/9/2006	2006	1	1	2	P1 - WK2
10	1/10/2006	2006	1	1	2	P1 - WK2
11	1/11/2006	2006	1	1	2	P1 - WK2
12	1/12/2006	2006	1	1	2	P1 - WK2
13	1/13/2006	2006	1	1	2	P1 - WK2
14	1/14/2006	2006	1	1	2	P1 - WK2
CalendarLookup

The date in that table is then matched up with the business date in another table and I am able to look at results by week, period, quarter or year (based on our fiscal calendar).

All of the data seems to be coming in just fine, and I have it all lined up. However, I am building some dynamic reporting on the front end using variations of GETPIVOTDATA. When I select a value to start the formula the format of the year is being changed from what I see in my lookup table and what comes across in the GETPIVOTDATA formula.

=GETPIVOTDATA("[Measures].[Sum of Amount]",$A$3,"[Enterprise_Sites].[Name]","[Enterprise_Sites].[Name].&[001 - St. George]","[Enterprise_PromoID].[Name]","[Enterprise_PromoID].[Name].&[Freq Diner Lrg Drink]","[CalendarLookup].[Year]","[CalendarLookup].[Year].&[2.014E3]","[CalendarLookup].[DateLookup]","[CalendarLookup].[DateLookup].&[P1 - WK2]")

Basically the only part that is causing me problems is the Year portion where it is "[Year].&[2.014E3]" where I would expect (based on the table) that it would be "[Year].&[2014]".

Anyone have any idea why the year format would be different than what is in my lookup table?

 

[ImkeF]

What you see it the scientific notation of 2014: https://support.office.com/en-za/ar...notation-f85a96c0-18a1-4249-81c3-e934cd2aae25

 

[Claymationator]

That may be correct, but it still doesn't seem to match the actual formatting for scientific notation that is available in Excel. I am not able to reproduce the actual "2.014E3" without getting an error. I can get something like "2.014E+03", but not exactly what comes across by default in the GETPIVOTDATA formula. Basically I am forced to use the "+" in the format.

In the actual sheet I am modifying the GETPIVOTDATA formula to be variable based on other inputs and data validation on a dashboard, so it has to match exactly what is in the pivot table, but the year isn't even formatted like that in the pivot table itself (see below in cell D4).

Excel 2012
	A	B	C	D	E	F	G
4				2014
5	Name	Name	Values	P1 - WK1	P1 - WK2	P1 - WK3	P1 - WK4
6	001 - St. George	25% Military Disc.	Sum of Amount
7			Sum of lCount
8		FB Free Drink	Sum of Amount
9			Sum of lCount
10		Freq Diner Juice	Sum of Amount
StorePromoPIVOT

I need to figure out why the format is coming across like that when it isn't formatted that way in the raw data, or in PowerPivot...but it is in the PivotTable that is based on the PowerPivot data.

 

[bolwahnn]

Hi Claymationator -
Were you able to find a solution to this problem? I'm having the exact same issue.

Thanks!

 

[SimonNU]

Maybe I'm reading this wrong... but if the data is stored in Power Pivot then shouldn't you be using cube formulas instead of GETPIVOTDATA?

They'll be much more reliable. You can even include slicers attached to your pivot table to capture that filter context.

 

[brodoplovac]

I had the same problem. The solution is to change year 2014 in Powerpivot from number to text.