<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 14 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
        {font-family:Wingdings;
        panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face
        {font-family:"Cambria Math";
        panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
        {font-family:Calibri;
        panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
        {font-family:Tahoma;
        panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
        {margin:0in;
        margin-bottom:.0001pt;
        font-size:11.0pt;
        font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
        {mso-style-priority:99;
        color:blue;
        text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
        {mso-style-priority:99;
        color:purple;
        text-decoration:underline;}
p.MsoAcetate, li.MsoAcetate, div.MsoAcetate
        {mso-style-priority:99;
        mso-style-link:"Balloon Text Char";
        margin:0in;
        margin-bottom:.0001pt;
        font-size:8.0pt;
        font-family:"Tahoma","sans-serif";}
span.EmailStyle17
        {mso-style-type:personal-compose;
        font-family:"Calibri","sans-serif";
        color:windowtext;}
span.BalloonTextChar
        {mso-style-name:"Balloon Text Char";
        mso-style-priority:99;
        mso-style-link:"Balloon Text";
        font-family:"Tahoma","sans-serif";}
.MsoChpDefault
        {mso-style-type:export-only;
        font-family:"Calibri","sans-serif";}
@page WordSection1
        {size:8.5in 11.0in;
        margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
        {page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-US" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal">Hey all, I&#8217;m having some problems getting a time dimension to play nicely with a simple calculated measure.&nbsp; The calculated measure is pretty simple, it looks at the previous member in time and returns that member to me.&nbsp; I&#8217;ve tried this
 with three time hierarchies:<o:p></o:p></p>
<p class="MsoNormal"><o:p>&nbsp;</o:p></p>
<p class="MsoNormal">Hierarchy #1:&nbsp; year -&gt; quarter -&gt; month -&gt; day<o:p></o:p></p>
<p class="MsoNormal">Hierarchy #2: &nbsp;year -&gt; week -&gt; day<o:p></o:p></p>
<p class="MsoNormal">Hierarchy #3: &nbsp;year -&gt; quarter -&gt; month<o:p></o:p></p>
<p class="MsoNormal"><o:p>&nbsp;</o:p></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:&quot;Times New Roman&quot;,&quot;serif&quot;">Here is a sample of the query I&#8217;m using:&nbsp;
<a href="http://pastebin.com/4j06KXzx">http://pastebin.com/4j06KXzx</a><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:&quot;Times New Roman&quot;,&quot;serif&quot;">Note that &#8220;All Periods&#8221; is representative of Hierarchy #1.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:&quot;Times New Roman&quot;,&quot;serif&quot;"><o:p>&nbsp;</o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:&quot;Times New Roman&quot;,&quot;serif&quot;">In any case, I&#8217;ve modified this query to work exclusively with the three hierarchies I listed above.&nbsp; Two columns are returned.&nbsp; The current value and the value in the
 previous step of time, for the current member.&nbsp; When I run this against hierarchies #2 &amp; #3, I get the values I expect in both columns.&nbsp; However, when I run it against hierarchy #1, the results are&#8230; off.&nbsp; The meat of the results start on line 478.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:&quot;Times New Roman&quot;,&quot;serif&quot;"><o:p>&nbsp;</o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:&quot;Times New Roman&quot;,&quot;serif&quot;">Results:&nbsp;
<a href="http://imageshack.us/photo/my-images/713/saikuoutput.jpg/">http://imageshack.us/photo/my-images/713/saikuoutput.jpg/</a><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:&quot;Times New Roman&quot;,&quot;serif&quot;"><o:p>&nbsp;</o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:&quot;Times New Roman&quot;,&quot;serif&quot;">As you can see, for the first few days of listed values for the month of April, prevMember results are as expected.&nbsp; The first column has data that is repeated and staggered
 in the second.&nbsp; However, after the first week has been completed, you can see how prevMember begins recycling results for the remainder of that month and then abruptly resets when the next month marker is reached.&nbsp; There shouldn&#8217;t have been values to stagger
 for the 2<sup>nd</sup> week and beyond.&nbsp; I&#8217;m having trouble understanding why hierarchy #2 &amp; #3 work flawlessly, but heirarchy #1 is presenting this oddity.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:&quot;Times New Roman&quot;,&quot;serif&quot;"><o:p>&nbsp;</o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:&quot;Times New Roman&quot;,&quot;serif&quot;">It may also be worth noting that heirarchy #1 seems to return correct data when I stop at the month level for results.&nbsp; Going into days causes problems.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:&quot;Times New Roman&quot;,&quot;serif&quot;"><o:p>&nbsp;</o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:&quot;Times New Roman&quot;,&quot;serif&quot;">Here is the xml behind hierarchy #1, if it helps:<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:&quot;Times New Roman&quot;,&quot;serif&quot;"><o:p>&nbsp;</o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:&quot;Times New Roman&quot;,&quot;serif&quot;">&lt;Hierarchy name=&quot;All Periods&quot; visible=&quot;true&quot; hasAll=&quot;true&quot; primaryKey=&quot;time_sk&quot; primaryKeyTable=&quot;dim_time&quot;&gt;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:&quot;Times New Roman&quot;,&quot;serif&quot;">&nbsp;&nbsp;&nbsp; &lt;Table name=&quot;dim_time&quot;&gt;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:&quot;Times New Roman&quot;,&quot;serif&quot;">&nbsp;&nbsp;&nbsp; &lt;/Table&gt;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:&quot;Times New Roman&quot;,&quot;serif&quot;">&nbsp;&nbsp;&nbsp; &lt;Level name=&quot;year&quot; visible=&quot;true&quot; column=&quot;year&quot; type=&quot;Numeric&quot; uniqueMembers=&quot;true&quot; levelType=&quot;TimeYears&quot; hideMemberIf=&quot;Never&quot; description=&quot;year&quot;&gt;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:&quot;Times New Roman&quot;,&quot;serif&quot;">&nbsp;&nbsp;&nbsp; &lt;/Level&gt;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:&quot;Times New Roman&quot;,&quot;serif&quot;">&nbsp;&nbsp;&nbsp; &lt;Level name=&quot;quarter&quot; visible=&quot;true&quot; column=&quot;quarter&quot; nameColumn=&quot;quarter_name&quot; type=&quot;Numeric&quot; uniqueMembers=&quot;false&quot; levelType=&quot;TimeQuarters&quot; hideMemberIf=&quot;Never&quot;
 description=&quot;quarter&quot;&gt;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:&quot;Times New Roman&quot;,&quot;serif&quot;">&nbsp;&nbsp;&nbsp; &lt;/Level&gt;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:&quot;Times New Roman&quot;,&quot;serif&quot;">&nbsp;&nbsp;&nbsp; &lt;Level name=&quot;month&quot; visible=&quot;true&quot; column=&quot;month_in_year&quot; nameColumn=&quot;month_long_name&quot; ordinalColumn=&quot;month_in_year&quot; type=&quot;Numeric&quot; uniqueMembers=&quot;false&quot; levelType=&quot;TimeMonths&quot;
 hideMemberIf=&quot;Never&quot; description=&quot;month&quot;&gt;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:&quot;Times New Roman&quot;,&quot;serif&quot;">&nbsp;&nbsp;&nbsp; &lt;/Level&gt;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:&quot;Times New Roman&quot;,&quot;serif&quot;">&nbsp;&nbsp;&nbsp; &lt;Level name=&quot;day&quot; visible=&quot;true&quot; column=&quot;day_in_month&quot; nameColumn=&quot;day_long_name&quot; ordinalColumn=&quot;day_in_month&quot; type=&quot;Numeric&quot; uniqueMembers=&quot;false&quot; levelType=&quot;TimeDays&quot;
 hideMemberIf=&quot;Never&quot; description=&quot;day&quot;&gt;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:&quot;Times New Roman&quot;,&quot;serif&quot;">&nbsp;&nbsp;&nbsp; &lt;/Level&gt;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:&quot;Times New Roman&quot;,&quot;serif&quot;">&lt;/Hierarchy&gt;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:&quot;Times New Roman&quot;,&quot;serif&quot;"><o:p>&nbsp;</o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:&quot;Times New Roman&quot;,&quot;serif&quot;"><o:p>&nbsp;</o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:&quot;Times New Roman&quot;,&quot;serif&quot;">If I can provide any more information, please let me know.&nbsp; Thanks for taking your time to help me out
</span><span style="font-size:12.0pt;font-family:Wingdings">J</span><span style="font-size:12.0pt;font-family:&quot;Times New Roman&quot;,&quot;serif&quot;"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:&quot;Times New Roman&quot;,&quot;serif&quot;"><o:p>&nbsp;</o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:&quot;Times New Roman&quot;,&quot;serif&quot;">Cheers,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:&quot;Times New Roman&quot;,&quot;serif&quot;"><o:p>&nbsp;</o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:&quot;Times New Roman&quot;,&quot;serif&quot;">Kris<o:p></o:p></span></p>
</div>
</body>
</html>