Pivot by 2 fields in Date table and reflect 2 different relationships for rows and columns

[ruve1k]

My fact table contains 2 date columns: Effective Date and Transaction Date.

Fact table
<table border="1" cellspacing="0"><tbody><tr><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="left" valign="bottom" width="129">Effective Date </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="left" valign="bottom" width="159">Transaction Date </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="left" valign="bottom" width="96">Amount </td></tr> <tr><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="left" valign="bottom" width="129">1/1/2008 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="left" valign="bottom" width="159">7/1/2008 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="96">10 </td></tr> <tr><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="left" valign="bottom" width="129">1/1/2008 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="left" valign="bottom" width="159">1/1/2009 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="96">3 </td></tr> <tr><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="left" valign="bottom" width="129">1/1/2008 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="left" valign="bottom" width="159">7/1/2009 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="96">2 </td></tr> <tr><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="left" valign="bottom" width="129">1/1/2008 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="left" valign="bottom" width="159">1/1/2010 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="96">1 </td></tr> <tr><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="left" valign="bottom" width="129">1/1/2009 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="left" valign="bottom" width="159">7/1/2009 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="96">15 </td></tr> <tr><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="left" valign="bottom" width="129">1/1/2009 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="left" valign="bottom" width="159">1/1/2010 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="96">8 </td></tr> <tr><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="left" valign="bottom" width="129">1/1/2009 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="left" valign="bottom" width="159">7/1/2010 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="96">6 </td></tr> <tr><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="left" valign="bottom" width="129">1/1/2009 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="left" valign="bottom" width="159">1/1/2011 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="96">5 </td></tr> <tr><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="left" valign="bottom" width="129">1/1/2010 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="left" valign="bottom" width="159">7/1/2010 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="96">13 </td></tr> <tr><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="left" valign="bottom" width="129">1/1/2010 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="left" valign="bottom" width="159">1/1/2011 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="96">5 </td></tr> <tr><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="left" valign="bottom" width="129">1/1/2010 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="left" valign="bottom" width="159">7/1/2011 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="96">2 </td></tr> <tr><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="left" valign="bottom" width="129">1/1/2011 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="left" valign="bottom" width="159">1/1/2011 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="96">11 </td></tr> <tr><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="left" valign="bottom" width="129">1/1/2011 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="left" valign="bottom" width="159">7/1/2011 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="96">6 </td></tr></tbody></table>
I would like to group the data by the Effective Year (based on Effective Date) as row labels, and by the Transaction Period (based on transaction date) as column labels.

Pivot Table
<table border="1" bordercolor="#999999" cellspacing="0"><tbody><tr><td rowspan="1" colspan="1" bgcolor="#DCE6F1" height="25.5" align="left" valign="bottom" width="166.5">Sum of Amount </td><td rowspan="1" colspan="1" bgcolor="#DCE6F1" height="25.5" align="left" valign="bottom" width="217.5">Transaction Period </td><td rowspan="1" colspan="1" bgcolor="#DCE6F1" height="25.5" align="right" valign="bottom" width="21">
</td><td rowspan="1" colspan="1" bgcolor="#DCE6F1" height="25.5" align="right" valign="bottom" width="31.5">
</td><td rowspan="1" colspan="1" bgcolor="#DCE6F1" height="25.5" align="right" valign="bottom" width="31.5">
</td><td rowspan="1" colspan="1" bgcolor="#DCE6F1" height="25.5" align="right" valign="bottom" width="31.5">
</td><td rowspan="1" colspan="1" bgcolor="#DCE6F1" height="25.5" align="right" valign="bottom" width="21">
</td></tr> <tr><td rowspan="1" colspan="1" bgcolor="#DCE6F1" height="25.5" align="left" valign="bottom" width="166.5">Effective Year </td><td rowspan="1" colspan="1" bgcolor="#DCE6F1" height="25.5" align="right" valign="bottom" width="217.5">2
</td><td rowspan="1" colspan="1" bgcolor="#DCE6F1" height="25.5" align="right" valign="bottom" width="21">3
</td><td rowspan="1" colspan="1" bgcolor="#DCE6F1" height="25.5" align="right" valign="bottom" width="31.5">4
</td><td rowspan="1" colspan="1" bgcolor="#DCE6F1" height="25.5" align="right" valign="bottom" width="31.5">5
</td><td rowspan="1" colspan="1" bgcolor="#DCE6F1" height="25.5" align="right" valign="bottom" width="31.5">6
</td><td rowspan="1" colspan="1" bgcolor="#DCE6F1" height="25.5" align="right" valign="bottom" width="21">7
</td></tr> <tr><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="166.5">2008 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="217.5">10 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="21">3 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="31.5">2 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="31.5">1 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="31.5">
</td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="21">
</td></tr> <tr><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="166.5">2009 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="217.5">
</td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="21">
</td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="31.5">15 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="31.5">8 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="31.5">6 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="21">5 </td></tr> <tr><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="166.5">2010 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="217.5">
</td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="21">
</td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="31.5">
</td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="31.5">13 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="31.5">5 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="21">2 </td></tr> <tr><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="166.5">2011 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="217.5">
</td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="21">
</td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="31.5">
</td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="31.5">
</td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="31.5">11 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="21">6 </td></tr> </tbody></table>
Say I now create relationships from both date columns in my fact table to the date column in my Dates table.

Dates table
<table border="1" cellspacing="0"><tbody><tr><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="left" valign="bottom" width="159">Date </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="left" valign="bottom" width="193.5">Year </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="left" valign="bottom" width="96">Period </td></tr> <tr><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="left" valign="bottom" width="159">1/1/2008 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="193.5">2008 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="96">1 </td></tr> <tr><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="left" valign="bottom" width="159">7/1/2008 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="193.5">2008 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="96">2 </td></tr> <tr><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="left" valign="bottom" width="159">1/1/2009 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="193.5">2009 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="96">3 </td></tr> <tr><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="left" valign="bottom" width="159">7/1/2009 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="193.5">2009 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="96">4 </td></tr> <tr><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="left" valign="bottom" width="159">1/1/2010 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="193.5">2010 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="96">5 </td></tr> <tr><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="left" valign="bottom" width="159">7/1/2010 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="193.5">2010 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="96">6 </td></tr> <tr><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="left" valign="bottom" width="159">1/1/2011 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="193.5">2011 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="96">7 </td></tr> <tr><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="left" valign="bottom" width="159">7/1/2011 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="193.5">2011 </td><td rowspan="1" colspan="1" bgcolor="#FFFFFF" height="25.5" align="right" valign="bottom" width="96">8 </td></tr></tbody></table>
Is there any way to create a measure that will reflect the Year context following the relationship with effective date, and reflect the Period context following the relationship with transaction date?

 

[powerpivotpro]

This is a PowerPivot V2 question correct?

Have you tried USERELATIONSHIP yet? I haven't had time to play with V2 much but I remember something like that being included.

 

[ruve1k]

Well, I am using v2, so a v2 solution would definitely be welcome. But I'll take a v1 solution as well. I'm not picky.

Yes, I have experimented with USERELATIONSHIP. The things I found challenging were:

1. It seems that it can really only be used as filter expression inside of a CALCULATE.
2. I am trying to use two different relationships simultaneously. And it seems that when nesting them the inner usage of USERELATIONSHIP over-rides the outer usage.

 

[David Churchward]

Have you tried calculating the year and period directly on your Fact table as a calculated column? Something like

Year := calculate(max(Dates[Year]),filter(all(Dates),Dates[Date]=FACT[Date1]))

Period :=
calculate(max(Dates[Month]),filter(all(Dates),Dates[Date]=FACT[Date2]))

I can't see another way of creating the values you need without using calculated columns on your fact table. USERELATIONSHIP won't work as this function only works on the ONE side of a MANY-ONE relationship (ie a Dimension) as a calculated column. Used the other way around, the measure ignores the relationship that you've specified and uses the active relationship instead.

These measures would therefore work in both V1 and V2 as there's no specific V2 functions used.

Hope this helps

 

[David Churchward]

I've just found Javier's post on this. I knew I had read it somewhere!

http://javierguillen.wordpress.com/2012/03/05/userelationship-and-direction-of-context-propagation/