stacking Prev period underneath

[masplin]

I have a pivot table that looks like this where April 2012 is a filter on "transaction month"











<table border="1" bordercolor="#999999" cellspacing="0"> <tbody><tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="243">Transaction Month </td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="114">April 2012 </td></tr> <tr><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="243">
</td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="114">
</td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="243">Values </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="114">
</td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="243">Load Value </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="114">£29,075 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="243">Prev Month Load Value </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="114">£25,537 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="243">Prev Year Load Value </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="114">£7,229 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="243">New Load Value </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="114">£14,772 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="243">Prev Month New Load Value </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="114">£13,626 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="243">Prev Year New Load Value </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="114">£4,729 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="243">Repeat Load Value </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="114">£14,303 </td></tr> </tbody></table>

Prev Month measure is this

CALCULATE(sum(transactions[Real  £]),transactions[Transaction]="add", PREVIOUSMONTH(transactions[Transaction Month]))

However this is horrible to look at and wondered it there is anyway to stack the differnet periods for each type underneath each other so it looks like this. Alternatively some way to create a set of dates to use as row lables that in this case would be April 2012, March 2012 and April 2011?







<table border="1" bordercolor="#999999" cellspacing="0"> <tbody><tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="243">Transaction Month </td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="114">April 2012 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="183"> </td></tr> <tr><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="243"> </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="114"> </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="183"> </td></tr> <tr><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="243"> </td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="114">Load Value </td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="183">New load Value </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="243">Current Month </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="114">£29,075 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="183">£14,772 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="243">Prev Month </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="114">£25,537 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="183">£13,626 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="243">Prev Year </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="114">£7,229 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="183">£4,729 </td></tr> </tbody></table>

Thanks for any advice Mike

 

[powerpivotpro]

If you pair a disconnected slicer table with the "branching measure" technique, you should be able to do this.

Disconnected slicer table would be 2 rows by 2 columns:

Caption ID
Load Value 1
New Load Value 2

Disconnected slicer technique described here:

http://www.powerpivotpro.com/2012/01/comparing-scientific-and-other-data-across-trials/

Then an example of branching measures described here:
http://www.powerpivotpro.com/2011/10/user-friendly-report-sorting-with-slicers/

Actually that second link has disconnected slicers in it too

Then rather than using your disconnected slicer table as a slicer, you put it on columns. And you write three branching measures that detect which context they are in, and then return either Load Value or New Load Value.

This all assumes that I have guessed your intent properly

 

[masplin]

Hi Rob

Both brilliant and useful tutorials I've book marked to use elsewhere, but not quite what I was after. Key diffenreces are:

1. I don't need the complete flexibilty of the slecers since I always want current month, previous month and same month a year ago so that can sort of be hard coded.
2. I have 10 measures for the current month so the way I'm doing it at the moement creatuing a new measure for each gives me a table 30 columns wide which is unreadable.

I tried something a bit like you suggested but it didn't work. I thought if I just had a list of relevant transactions months in the row column that would act as a filter on my standard 10 measures and I wouldn't need to create the other 20. So I made a table April 2012, March 2012 and April 2011 called it Report month ands added it to Powerpivot. I then created a relationship between these dates and the Transaction month. However it would produce anything when I dropped the "report month" field onto the row column. It might not have helped in the long run anyway.



Essentially I can produce my report by just using transaction month and clicking off the ones I don't want, but firstly this sin't very neat and more importantly I can't work out how to calc the % change from Prevmonth to current month etc.


To be clearer on what i'm aiming for i have created it by using GETPIVOTDATA functions from a verison that has every month so a 2 step process











<table border="1" bordercolor="#999999" cellspacing="0"> <tbody><tr><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="78" valign="bottom" width="133.5"> </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="78" valign="bottom" width="132">Apr-12 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="78" valign="bottom" width="94.5">Load Value </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="78" valign="bottom" width="99">New Load Value </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="78" valign="bottom" width="96">Repeat Load Value </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="78" valign="bottom" width="78">Spend Value </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="78" valign="bottom" width="93">New Spend Value </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="78" valign="bottom" width="94.5">Repeat Spend Value </td></tr> <tr><td rowspan="1" colspan="1" align="right" bgcolor="#F2DCDB" height="27" valign="bottom" width="133.5">Apr-12 </td><td rowspan="1" colspan="1" align="right" bgcolor="#F2DCDB" height="27" valign="bottom" width="132">Last Month </td><td rowspan="1" colspan="1" align="right" bgcolor="#F2DCDB" height="27" valign="bottom" width="94.5">£29,075 </td><td rowspan="1" colspan="1" align="right" bgcolor="#F2DCDB" height="27" valign="bottom" width="99">£14,772 </td><td rowspan="1" colspan="1" align="right" bgcolor="#F2DCDB" height="27" valign="bottom" width="96">£14,303 </td><td rowspan="1" colspan="1" align="right" bgcolor="#F2DCDB" height="27" valign="bottom" width="78">£28,171 </td><td rowspan="1" colspan="1" align="right" bgcolor="#F2DCDB" height="27" valign="bottom" width="93">£11,880 </td><td rowspan="1" colspan="1" align="right" bgcolor="#F2DCDB" height="27" valign="bottom" width="94.5">£16,291 </td></tr> <tr><td rowspan="1" colspan="1" align="right" bgcolor="#E4DFEC" height="25.5" valign="bottom" width="133.5">Mar-12 </td><td rowspan="1" colspan="1" align="right" bgcolor="#E4DFEC" height="25.5" valign="bottom" width="132">Previous Month </td><td rowspan="1" colspan="1" align="right" bgcolor="#E4DFEC" height="25.5" valign="bottom" width="94.5">£25,537 </td><td rowspan="1" colspan="1" align="right" bgcolor="#E4DFEC" height="25.5" valign="bottom" width="99">£13,626 </td><td rowspan="1" colspan="1" align="right" bgcolor="#E4DFEC" height="25.5" valign="bottom" width="96">£11,911 </td><td rowspan="1" colspan="1" align="right" bgcolor="#E4DFEC" height="25.5" valign="bottom" width="78">£23,696 </td><td rowspan="1" colspan="1" align="right" bgcolor="#E4DFEC" height="25.5" valign="bottom" width="93">£10,164 </td><td rowspan="1" colspan="1" align="right" bgcolor="#E4DFEC" height="25.5" valign="bottom" width="94.5">£13,532 </td></tr> <tr><td rowspan="1" colspan="1" align="right" bgcolor="#E4DFEC" height="25.5" valign="bottom" width="133.5"> </td><td rowspan="1" colspan="1" align="right" bgcolor="#E4DFEC" height="25.5" valign="bottom" width="132">vs Prev Month </td><td rowspan="1" colspan="1" align="right" bgcolor="#E4DFEC" height="25.5" valign="bottom" width="94.5">14% </td><td rowspan="1" colspan="1" align="right" bgcolor="#E4DFEC" height="25.5" valign="bottom" width="99">8% </td><td rowspan="1" colspan="1" align="right" bgcolor="#E4DFEC" height="25.5" valign="bottom" width="96">20% </td><td rowspan="1" colspan="1" align="right" bgcolor="#E4DFEC" height="25.5" valign="bottom" width="78">19% </td><td rowspan="1" colspan="1" align="right" bgcolor="#E4DFEC" height="25.5" valign="bottom" width="93">17% </td><td rowspan="1" colspan="1" align="right" bgcolor="#E4DFEC" height="25.5" valign="bottom" width="94.5">20% </td></tr> <tr><td rowspan="1" colspan="1" align="right" bgcolor="#EBF1DE" height="25.5" valign="bottom" width="133.5"> </td><td rowspan="1" colspan="1" align="right" bgcolor="#EBF1DE" height="25.5" valign="bottom" width="132">3M Rolling </td><td rowspan="1" colspan="1" align="right" bgcolor="#EBF1DE" height="25.5" valign="bottom" width="94.5">£21,227 </td><td rowspan="1" colspan="1" align="right" bgcolor="#EBF1DE" height="25.5" valign="bottom" width="99">£11,252 </td><td rowspan="1" colspan="1" align="right" bgcolor="#EBF1DE" height="25.5" valign="bottom" width="96">£9,975 </td><td rowspan="1" colspan="1" align="right" bgcolor="#EBF1DE" height="25.5" valign="bottom" width="78">£19,437 </td><td rowspan="1" colspan="1" align="right" bgcolor="#EBF1DE" height="25.5" valign="bottom" width="93">£8,036 </td><td rowspan="1" colspan="1" align="right" bgcolor="#EBF1DE" height="25.5" valign="bottom" width="94.5">£11,400 </td></tr> <tr><td rowspan="1" colspan="1" align="right" bgcolor="#EBF1DE" height="25.5" valign="bottom" width="133.5"> </td><td rowspan="1" colspan="1" align="right" bgcolor="#EBF1DE" height="25.5" valign="bottom" width="132">vs 3M </td><td rowspan="1" colspan="1" align="right" bgcolor="#EBF1DE" height="25.5" valign="bottom" width="94.5">37% </td><td rowspan="1" colspan="1" align="right" bgcolor="#EBF1DE" height="25.5" valign="bottom" width="99">31% </td><td rowspan="1" colspan="1" align="right" bgcolor="#EBF1DE" height="25.5" valign="bottom" width="96">43% </td><td rowspan="1" colspan="1" align="right" bgcolor="#EBF1DE" height="25.5" valign="bottom" width="78">45% </td><td rowspan="1" colspan="1" align="right" bgcolor="#EBF1DE" height="25.5" valign="bottom" width="93">48% </td><td rowspan="1" colspan="1" align="right" bgcolor="#EBF1DE" height="25.5" valign="bottom" width="94.5">43% </td></tr> <tr><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="25.5" valign="bottom" width="133.5">Apr-11 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="25.5" valign="bottom" width="132">Previous Year </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="25.5" valign="bottom" width="94.5">£7,229 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="25.5" valign="bottom" width="99">£4,729 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="25.5" valign="bottom" width="96">£2,501 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="25.5" valign="bottom" width="78">£5,961 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="25.5" valign="bottom" width="93">£2,973 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="25.5" valign="bottom" width="94.5">£2,988 </td></tr> <tr><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="27" valign="bottom" width="133.5"> </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="27" valign="bottom" width="132">vs Prev Year </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="27" valign="bottom" width="94.5">302% </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="27" valign="bottom" width="99">212% </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="27" valign="bottom" width="96">472% </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="27" valign="bottom" width="78">373% </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="27" valign="bottom" width="93">300% </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="27" valign="bottom" width="94.5">445% </td></tr> </tbody></table>


Thanks again


Mike

 

[powerpivotpro]

You can use the disconnected "slicer" field on columns though, and that at least SEEMS like it would help. Make sense? (I do it all the time and it seems like the same problem you are facing)

 

[masplin]

I'm obviously being a bit stupid with my syntax as understand what you mean

So 1st step create a table April 12, mar 12 and Apr 11. Link it to powerpivot called "ReportDate".


-- removed inline image ---

Start a new pivot and drop reportdate[report date] into row labels.

My original definition of load value, when I have all transaction months is

CALCULATE(sum(transactions[Real  £]),transactions[Transaction]="add")

so seems logical to create a new measure that links the transaction month to these months now in the rows. So I tried this

=CALCULATE(sum(transactions[Real  £]),filter(transactions,transactions[Transaction Month]=ReportDate[ReportDate] && transactions[Transaction]="add"))

However this just says "The value for column 'ReportDate' in table 'ReportDate' cannot be determined in the current context. Check that all columns referenced in the calculation expression exist, and that there are no circular dependencies.".

I assume I'm skipping a step somehwere, but can't fathom it. Transaction Month and Reportdate are both 1st of the month and formatted "mmmm yyyy"

 

[powerpivotpro]

Any chance you can send me your workbook, or a version of it that has the sensitive values scrambled? The quickest way, for both of us, will be if I can directly see it, and send it back fixed, THEN explain

Email info in signature.

 

[masplin]

Hi Rob. Will try and make something small. How do I get it to you please?

Thanks Mike