# Use of powerpivot for comparing unknown related periods



## Topdown (Jan 30, 2012)

Hi all, 
I am experienced in Excel, Databases and but just getting to grips with Powerpivot.

*Challenge:*

I have a table in SQL Server with records pertaining to a period (column 1). This table is populated with the same keys (column 2) for other periods also.
   Example:
  <table class="MsoNormalTable" style="width:235.0pt;margin-left:4.7pt;border-collapse:collapse;mso-yfti-tbllook:  1184;mso-padding-alt:0cm 5.4pt 0cm 5.4pt" width="313" border="0" cellpadding="0" cellspacing="0">  <tbody><tr style="mso-yfti-irow:0;mso-yfti-firstrow:yes;height:15.0pt">   <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="64" nowrap="nowrap">   *Period
*    </td>   <td style="width:91.0pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="121" nowrap="nowrap">   *Key for period
*    </td>   <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="64" nowrap="nowrap">   *Value1
*    </td>   <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="64" nowrap="nowrap">   *Value2
*    </td>  </tr>  <tr style="mso-yfti-irow:1;height:15.0pt">   <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="64" nowrap="nowrap">   1​   </td>   <td style="width:91.0pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="121" nowrap="nowrap">   111​   </td>   <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="64" nowrap="nowrap">   90​   </td>   <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="64" nowrap="nowrap">   12​   </td>  </tr>  <tr style="mso-yfti-irow:2;height:15.0pt">   <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="64" nowrap="nowrap">   1​   </td>   <td style="width:91.0pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="121" nowrap="nowrap">   112​   </td>   <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="64" nowrap="nowrap">   80​   </td>   <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="64" nowrap="nowrap">   12​   </td>  </tr>  <tr style="mso-yfti-irow:3;height:15.0pt">   <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="64" nowrap="nowrap">   1​   </td>   <td style="width:91.0pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="121" nowrap="nowrap">   113​   </td>   <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="64" nowrap="nowrap">   70​   </td>   <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="64" nowrap="nowrap">   10​   </td>  </tr>  <tr style="mso-yfti-irow:4;height:15.0pt">   <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="64" nowrap="nowrap">   1​   </td>   <td style="width:91.0pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="121" nowrap="nowrap">   114​   </td>   <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="64" nowrap="nowrap">   60​   </td>   <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="64" nowrap="nowrap">   10​   </td>  </tr>  <tr style="mso-yfti-irow:5;height:15.0pt">   <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="64" nowrap="nowrap">   2​   </td>   <td style="width:91.0pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="121" nowrap="nowrap">   111​   </td>   <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="64" nowrap="nowrap">   80​   </td>   <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="64" nowrap="nowrap">   12​   </td>  </tr>  <tr style="mso-yfti-irow:6;height:15.0pt">   <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="64" nowrap="nowrap">   2​   </td>   <td style="width:91.0pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="121" nowrap="nowrap">   112​   </td>   <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="64" nowrap="nowrap">   70​   </td>   <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="64" nowrap="nowrap">   10​   </td>  </tr>  <tr style="mso-yfti-irow:7;height:15.0pt">   <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="64" nowrap="nowrap">   2​   </td>   <td style="width:91.0pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="121" nowrap="nowrap">   113​   </td>   <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="64" nowrap="nowrap">   60​   </td>   <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="64" nowrap="nowrap">   10​   </td>  </tr>  <tr style="mso-yfti-irow:8;mso-yfti-lastrow:yes;height:15.0pt">   <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="64" nowrap="nowrap">   2​   </td>   <td style="width:91.0pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="121" nowrap="nowrap">   114​   </td>   <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="64" nowrap="nowrap">   50​   </td>   <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="64" nowrap="nowrap">   8​   </td>  </tr> </tbody></table>   
  If it assists, you could think of period 1 as Q1, period 2 as Q2 etc, and I wish to see how two periods vary in terms of their values, both in absolute numerical difference, but also % difference. 

  Sample un-pivoted results would therefore look something like this:

  <table class="MsoNormalTable" style="width:457.35pt;margin-left:4.7pt;border-collapse:collapse;mso-yfti-tbllook:  1184;mso-padding-alt:0cm 5.4pt 0cm 5.4pt" width="610" border="0" cellpadding="0" cellspacing="0">  <tbody><tr style="mso-yfti-irow:0;mso-yfti-firstrow:yes;height:15.0pt">   <td style="width:78.35pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="104" nowrap="nowrap">   *Key for period
*    </td>   <td style="width:45.35pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="60" nowrap="nowrap">   *Value1
*    </td>   <td style="width:45.35pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="60" nowrap="nowrap">   *Value2
*    </td>   <td style="width:62.0pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="83" nowrap="nowrap">   *Value 1Diff
*    </td>   <td style="width:62.8pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="84" nowrap="nowrap">   *Value2Diff
*    </td>   <td style="width:81.75pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="109" nowrap="nowrap">   *Value1Percent
*    </td>   <td style="width:81.75pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="109" nowrap="nowrap">   *Value2Percent
*    </td>  </tr>  <tr style="mso-yfti-irow:1;height:15.0pt">   <td style="width:78.35pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="104" nowrap="nowrap">   111​   </td>   <td style="width:45.35pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="60" nowrap="nowrap">   90​   </td>   <td style="width:45.35pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="60" nowrap="nowrap">   12​   </td>   <td style="width:62.0pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="83" nowrap="nowrap">   10​   </td>   <td style="width:62.8pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="84" nowrap="nowrap">   0​   </td>   <td style="width:81.75pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="109" nowrap="nowrap">   11%​   </td>   <td style="width:81.75pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="109" nowrap="nowrap">   0%​   </td>  </tr>  <tr style="mso-yfti-irow:2;height:15.0pt">   <td style="width:78.35pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="104" nowrap="nowrap">   112​   </td>   <td style="width:45.35pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="60" nowrap="nowrap">   80​   </td>   <td style="width:45.35pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="60" nowrap="nowrap">   12​   </td>   <td style="width:62.0pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="83" nowrap="nowrap">   10​   </td>   <td style="width:62.8pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="84" nowrap="nowrap">   2​   </td>   <td style="width:81.75pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="109" nowrap="nowrap">   13%​   </td>   <td style="width:81.75pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="109" nowrap="nowrap">   17%​   </td>  </tr>  <tr style="mso-yfti-irow:3;height:15.0pt">   <td style="width:78.35pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="104" nowrap="nowrap">   113​   </td>   <td style="width:45.35pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="60" nowrap="nowrap">   70​   </td>   <td style="width:45.35pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="60" nowrap="nowrap">   10​   </td>   <td style="width:62.0pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="83" nowrap="nowrap">   10​   </td>   <td style="width:62.8pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="84" nowrap="nowrap">   0​   </td>   <td style="width:81.75pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="109" nowrap="nowrap">   14%​   </td>   <td style="width:81.75pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="109" nowrap="nowrap">   0%​   </td>  </tr>  <tr style="mso-yfti-irow:4;mso-yfti-lastrow:yes;height:15.0pt">   <td style="width:78.35pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="104" nowrap="nowrap">   114​   </td>   <td style="width:45.35pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="60" nowrap="nowrap">   60​   </td>   <td style="width:45.35pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="60" nowrap="nowrap">   10​   </td>   <td style="width:62.0pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="83" nowrap="nowrap">   10​   </td>   <td style="width:62.8pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="84" nowrap="nowrap">   2​   </td>   <td style="width:81.75pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="109" nowrap="nowrap">   17%​   </td>   <td style="width:81.75pt;padding:0cm 5.4pt 0cm 5.4pt;   height:15.0pt" valign="bottom" width="109" nowrap="nowrap">   20%​   </td>  </tr> </tbody></table>   

  I have seen the examples such as budget vs actual here: http://www.powerpivotpro.com/2012/01/salesbudget-integrating-data-of-different-grains/ however,  it is not clear to me how to apply this logic to my problem. 

  It seems that my only option would be to bring each period back as a distinct table, and link them by the [KeyForPeriod] column in Powerpivot and compute the measures.

  This seems to have a number of disadvantages below


Since I have tens of “value” column fields it seems very onerous to create a “diff” column and a “percentage diff” column for each value upon import of new data.
   The import of periods (ie 1 through n) will have to be performed by the user (although I expect we could write some VBA to perform this).
Such a result could be done with a pivot table. What use is powerpivot here?
   Are there any other solutions to the problem? 
  Note, I have full control over the database, so if it is possible we can generate a more useful schema for input.

  Best regards and thanks for your answers in advance.
  Topdown.


----------



## powerpivotpro (Jan 30, 2012)

Clarifying q's:

1) Is the combination of Period and KeyForPeriod unique?  Meaning, will there only ever be one row of data that is Period=1 and KeyForPeriod=111?

2) How many values of Period will there be?  Just 1 and 2, or lots of values?


----------



## Topdown (Jan 30, 2012)

Hi Rob, I feel privileged for having you look into my question!



powerpivotpro said:


> 1) Is the combination of Period and KeyForPeriod unique?  Meaning, will there only ever be one row of data that is Period=1 and KeyForPeriod=111?


Yes, this will be unique.




powerpivotpro said:


> 2) How many values of Period will there be?  Just 1 and 2, or lots of values?


Potentially lots of values. 


It might help to give you some context (the term _"period_" was used in order to be more familiar for readers).

We are running some modelling whereby all the records indexed by  KeyPerPeriod are run once, and then run a n'th time that might produce  different results. I wish to compare the output of two executions.  

Therefore, for each _period_/_execution_ we can be guaranteed that the number of records, and the keyForPeriod in each period/execution exist in each period set.

The number of value fields that we wish to compare is also large (20+).

Many thanks again!


----------



## powerpivotpro (Jan 30, 2012)

I think we have worked this out "offline" and my blog post tomorrow will cover it.  I will link to the post here once it is up


----------



## powerpivotpro (Jan 31, 2012)

OK, the solution I provided is now the subject of a blog post, including a link to download the workbook 

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


----------

