pivot dates

[kin1]

hi,

is there a way to group dates from jun-2012 to may-2013 and the second row of dates from Jun-2013 to May-2014?

i want to compare the profits from previous year/month.

it will kind of look like this, instead of the months, the data below will fill those spots where the dates are located.

[TABLE="width: 791"]
<colgroup><col><col span="11"></colgroup><tbody>[TR]
[TD][/TD]
[TD="align: right"]Jun-12[/TD]
[TD="align: right"]Aug-12[/TD]
[TD="align: right"]Sep-12[/TD]
[TD="align: right"]Oct-12[/TD]
[TD="align: right"]Nov-12[/TD]
[TD="align: right"]Dec-12[/TD]
[TD="align: right"]Jan-13[/TD]
[TD="align: right"]Feb-13[/TD]
[TD="align: right"]Mar-13[/TD]
[TD="align: right"]Apr-13[/TD]
[TD="align: right"]May-13
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]Jun-13[/TD]
[TD="align: right"]Aug-13[/TD]
[TD="align: right"]Sep-13[/TD]
[TD="align: right"]Oct-13[/TD]
[TD="align: right"]Nov-13[/TD]
[TD="align: right"]Dec-13[/TD]
[TD="align: right"]Jan-14[/TD]
[TD="align: right"]Feb-14[/TD]
[TD="align: right"]Mar-14[/TD]
[TD="align: right"]Apr-14[/TD]
[TD="align: right"]May-14[/TD]
[/TR]
[TR]
[TD]code[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]abc[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD]xyz[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[/TR]
</tbody>[/TABLE]

 

[kin1]

hi,

is there a way to group dates from jun-2013 to may-2014 and the second row of dates from Jun-2013 to May-2014?

i want to compare the profits from previous year/month.

row one will be jun 2013 to may 2014
the second row will be jun 2014 to may 2015

[TABLE="width: 1400"]
<colgroup><col><col><col span="11"></colgroup><tbody>[TR]
[TD]
[/TD]
[TD="align: right"]Jun-13[/TD]
[TD="align: right"]Jul-13[/TD]
[TD="align: right"]Aug-13[/TD]
[TD="align: right"]Sep-13[/TD]
[TD="align: right"]Oct-13[/TD]
[TD="align: right"]Nov-13[/TD]
[TD="align: right"]Dec-13[/TD]
[TD="align: right"]Jan-14[/TD]
[TD="align: right"]Feb-14[/TD]
[TD="align: right"]Mar-14[/TD]
[TD="align: right"]Apr-14[/TD]
[TD="align: right"]May-14[/TD]
[/TR]
[TR]
[TD]xyz[/TD]
[TD] $ 71,821.10[/TD]
[TD] $ 89,982.20[/TD]
[TD] $ 108,143.30[/TD]
[TD] $ 126,304.40[/TD]
[TD] $ 144,465.50[/TD]
[TD] $ 162,626.60[/TD]
[TD] $ 180,787.70[/TD]
[TD] $ 198,948.80[/TD]
[TD] $ 217,109.90[/TD]
[TD] $ 235,271.00[/TD]
[TD] $ 253,432.10[/TD]
[TD] $ 271,593.20[/TD]
[/TR]
[TR]
[TD]xyz[/TD]
[TD] $ 289,754.30[/TD]
[TD] $ 307,915.40[/TD]
[TD] $ 326,076.50[/TD]
[TD] $ 344,237.60[/TD]
[TD] $ 362,398.70[/TD]
[TD] $ 380,559.80[/TD]
[TD] $ 398,720.90[/TD]
[TD] $ 416,882.00[/TD]
[TD] $ 435,043.10[/TD]
[TD] $ 453,204.20[/TD]
[TD] $ 471,365.30[/TD]
[TD] $ 489,526.40[/TD]
[/TR]
</tbody>[/TABLE]

11
[/QUOTE]

 

[scottsen]

Can you confirm you are using Power Pivot (and this isn't just a "normal" pivot table question)?

Assuming so, do you have a "Calendar" lookup table?

 

[kin1]

hi scott, actually the one i paste was from data i made up in a regular pivot. i ended up build a column check using the datavalue with a if statement.

i'm dont evne know what a calendar lookup table is in the power pivot. i've been using it just like the regular pivot. can you tell me if there's a row limit with power pivot? i try to import a excel file with about 700k rows and about 14 columns and it's telling me i need more resource. i thought the power pivot can handle a lot more data processing. i have 18gig of ram and im running a i7 2nd generation. the only thing i can think of is upgrading excel to a 64bit, but im not sure if that will resolve my problem.

 

[scottsen]

Power Pivot can certainly handle 700k rows. 64 bit DOES make a pretty big difference though.

In general, if you are doing ANYTHING with dates in power pivot, you are going to want to have a separate lookup table for your calendar, that has things like WeekNum, QuarterNum, MonthName, Year, etc. It will allow you do easily group your data into ... whatever date-shape you want.

 

[kin1]

thanks scott.. i realized the date lookup table when i tried to perform a vlookup in power pivot, which didnt exist!.