Failing to understand concepts behind Time Intelligent functions

[masplin]

I have a transaction table that runs to 14th may and want to compare various metrics with the first 14 days of april. In the transaction table I have columns Date (dd-mm-yy hh:mm:ss) which comes from the database, and have calculated Trans Day (DD MMM YY), Trans Month (01 MMM YY).

My first attempt was to use transaction month as rows with the previous period calc as follows

CALCULATE(counta(transactions[Transaction]),filter(transactions,transactions[Transaction]="spend" && DATEADD(transactions[Transaction Day],-1,month)))

this produced a pivot like this. I assumed that becuase my row label is actually 1st May 2012 then the DATEADD is going back to month=1st April 2012 which maps to every day in April. What is odd that for Feb & Mar11 the numbers arent the same but are for others?

<table border="1" bordercolor="#999999" cellspacing="0"><tbody><tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="145.5">Row Labels </td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">Purchases </td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="150">Prev Period Purch </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="145.5">February, 2011 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">269 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="150">97 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="145.5">March, 2011 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">648 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="150">636 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="145.5">April, 2011 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">875 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="150">875 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="145.5">May, 2011 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">1219 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="150">1219 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="145.5">June, 2011 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">1160 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="150">1160 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="145.5">July, 2011 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">1221 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="150">1221 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="145.5">August, 2011 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">1172 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="150">1172 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="145.5">September, 2011 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">1136 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="150">1136 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="145.5">October, 2011 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">1149 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="150">1149 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="145.5">November, 2011 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">1385 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="150">1385 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="145.5">December, 2011 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">1120 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="150">1120 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="145.5">January, 2012 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">2575 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="150">2575 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="145.5">February, 2012 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">2661 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="150">2661 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="145.5">March, 2012 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">3160 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="150">3160 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="145.5">April, 2012 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">3797 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="150">3797 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="145.5">May, 2012 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">1922 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="150">1922 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="145.5">Grand Total </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">25469 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="150">25285 </td></tr> </tbody></table>
I read around and found that I should have created a date table (DailyStats) so did that with Day (dd mmm yy), Monthshort (MMM) and Year (YYYY) columns. I created a relationship between transaction Day and Day. This time my measure for previous period was

CALCULATE(counta(transactions[Transaction]),filter(transactions,transactions[Transaction]="spend" && DATEADD(DailyStats[Day],-1,month)))

This time I used MonthShort and Year as row labels. Again most months it made no differnece except Feb was differnet!! It looked like this. My expectation was that 2012 May only has days 1-14th so shouldn't April also be 1-14th? I'm really baffled by how to use time intelligent functions so would be grateful if someone could explain the principals as clearly way off track with my logic!

<table border="1" bordercolor="#999999" cellspacing="0"><tbody><tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="127.5">Row Labels </td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">Purchases </td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="190.5">Prev Period Purchases </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="127.5">2011 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">11354 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="190.5">11193 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="127.5">Apr </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">875 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="190.5">875 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="127.5">Aug </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">1172 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="190.5">1172 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="127.5">Dec </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">1120 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="190.5">1120 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="127.5">Feb </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">269 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="190.5">108 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="127.5">Jul </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">1221 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="190.5">1221 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="127.5">Jun </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">1160 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="190.5">1160 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="127.5">Mar </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">648 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="190.5">648 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="127.5">May </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">1219 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="190.5">1219 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="127.5">Nov </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">1385 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="190.5">1385 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="127.5">Oct </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">1149 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="190.5">1149 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="127.5">Sep </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">1136 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="190.5">1136 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="127.5">2012 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">14115 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="190.5">14115 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="127.5">Apr </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">3797 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="190.5">3797 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="127.5">Feb </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">2661 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="190.5">2661 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="127.5">Jan </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">2575 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="190.5">2575 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="127.5">Mar </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">3160 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="190.5">3160 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="127.5">May </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">1922 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="190.5">1922 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="127.5">Grand Total </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">25469 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="190.5">25308 </td></tr> </tbody></table>

 

[powerpivotpro]

Try rewriting your measure as:

CALCULATE(counta(transactions[Transaction]), transactions[Transaction]="spend", DATEADD(DailyStats[Day],-1,month))

and see if you get better results.

 

[masplin]

Hi Rob. Same result I'm afraid.

 

[masplin]

Hmm now it's changed as found a bug in the current formula. Moving in right direction as now looks like this so looking back 1 month. However May 2012 only has 14 days in the Date table so was expecting to only get the 1st 14 days of April?

For my education what is the difference between

CALCULATE(counta(transactions[Transaction]),transactions[Transaction]="spend",DATEADD(DailyStats[Day],-1,month))

and

CALCULATE(counta(transactions[Transaction]),filter(transactions,transactions[Transaction]="spend" && DATEADD(DailyStats[Day],-1,month)))

I used the 2nd version as found the first style sometimes throws errors if any of the arguements are Field 1 = field 2. I thought it was just a more complicated way of doing the same thing, but clearly not. Thanks again for your advice as realise I'm being a complete pain! Trying to learn enough to start contributing some answers.

<table border="1" bordercolor="#999999" cellspacing="0"> <tbody><tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="127.5">Row Labels </td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">Purchases </td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="190.5">Prev Period Purchases </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="127.5">2011 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">11354 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="190.5">10234 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="127.5">Feb </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">269 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="190.5">
</td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="127.5">Mar </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">648 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="190.5">269 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="127.5">Apr </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">875 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="190.5">648 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="127.5">May </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">1219 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="190.5">875 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="127.5">Jun </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">1160 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="190.5">1219 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="127.5">Jul </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">1221 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="190.5">1160 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="127.5">Aug </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">1172 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="190.5">1221 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="127.5">Sep </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">1136 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="190.5">1172 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="127.5">Oct </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">1149 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="190.5">1136 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="127.5">Nov </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">1385 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="190.5">1149 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="127.5">Dec </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">1120 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="190.5">1385 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="127.5">2012 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">14115 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="190.5">13313 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="127.5">Jan </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">2575 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="190.5">1120 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="127.5">Feb </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">2661 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="190.5">2575 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="127.5">Mar </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">3160 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="190.5">2661 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="127.5">Apr </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">3797 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="190.5">3160 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="127.5">May </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">1922 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="190.5">3797 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="127.5">Grand Total </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="93">25469 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="190.5">23547 </td></tr> </tbody></table>

 

[masplin]

Carried on fidling and if I add the day of month to rows seems to being doing exactly what you would expect. I can't understnad why the total for Prev Month in May isn't the total of the 1st 14 days and is the total for all of April
<table border="0" cellpadding="0" cellspacing="0" width="304"><colgroup><col style="mso-width-source:userset;mso-width-alt:4906;width:86pt" width="115"> <col style="mso-width-source:userset;mso-width-alt:2645;width:47pt" width="62"> <col style="mso-width-source:userset;mso-width-alt:5418;width:95pt" width="127"> </colgroup><tbody><tr style="height:12.0pt" height="16"> <td style="height:12.0pt;width:86pt;font-size:9.0pt; color:white;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border-top:none;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none;background:#366092; mso-pattern:#366092 none" height="16" width="115">Row Labels</td> <td style="width:47pt;font-size:9.0pt;color:white;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:none;border-right:none;border-bottom:.5pt solid #B8CCE4; border-left:none;background:#366092;mso-pattern:#366092 none" width="62">Purchases</td> <td style="width:95pt;font-size:9.0pt;color:white;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:none;border-right:none;border-bottom:.5pt solid #B8CCE4; border-left:none;background:#366092;mso-pattern:#366092 none" width="127">Prev Period Purchases</td> </tr> <tr style="height:12.0pt" height="16"> <td class="xl160" style="height:12.0pt;font-size:9.0pt;color:white; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none;background:#95B3D7; mso-pattern:#95B3D7 none" height="16">2011</td> <td style="font-size:9.0pt;color:white;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none;background:#95B3D7; mso-pattern:#95B3D7 none" align="right">11354</td> <td style="font-size:9.0pt;color:white;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none;background:#95B3D7; mso-pattern:#95B3D7 none" align="right">10234</td> </tr> <tr style="height:12.0pt" height="16"> <td class="xl160" style="height:12.0pt;font-size:9.0pt;color:white; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none;background:#95B3D7; mso-pattern:#95B3D7 none" height="16">2012</td> <td style="font-size:9.0pt;color:white;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none;background:#95B3D7; mso-pattern:#95B3D7 none" align="right">14115</td> <td style="font-size:9.0pt;color:white;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none;background:#95B3D7; mso-pattern:#95B3D7 none" align="right">13313</td> </tr> <tr style="height:12.0pt" height="16"> <td class="xl161" style="height:12.0pt;font-size:9.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #4F81BD;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" height="16">Jan</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #4F81BD;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">2575</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #4F81BD;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">1120</td> </tr> <tr style="height:12.0pt" height="16"> <td class="xl161" style="height:12.0pt;font-size:9.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #4F81BD;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" height="16">Feb</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #4F81BD;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">2661</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #4F81BD;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">2575</td> </tr> <tr style="height:12.0pt" height="16"> <td class="xl161" style="height:12.0pt;font-size:9.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #4F81BD;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" height="16">Mar</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #4F81BD;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">3160</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #4F81BD;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">2661</td> </tr> <tr style="height:12.0pt" height="16"> <td class="xl161" style="height:12.0pt;font-size:9.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #4F81BD;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" height="16">Apr</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #4F81BD;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">3797</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #4F81BD;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">3160</td> </tr> <tr style="height:12.0pt" height="16"> <td class="xl162" style="height:12.0pt;font-size:9.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" height="16">01 April 2012</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">92</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">91</td> </tr> <tr style="height:12.0pt" height="16"> <td class="xl162" style="height:12.0pt;font-size:9.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" height="16">02 April 2012</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">108</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">126</td> </tr> <tr style="height:12.0pt" height="16"> <td class="xl162" style="height:12.0pt;font-size:9.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" height="16">03 April 2012</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">159</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">102</td> </tr> <tr style="height:12.0pt" height="16"> <td class="xl162" style="height:12.0pt;font-size:9.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" height="16">04 April 2012</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">113</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">124</td> </tr> <tr style="height:12.0pt" height="16"> <td class="xl162" style="height:12.0pt;font-size:9.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" height="16">05 April 2012</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">99</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">123</td> </tr> <tr style="height:12.0pt" height="16"> <td class="xl162" style="height:12.0pt;font-size:9.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" height="16">06 April 2012</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">105</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">126</td> </tr> <tr style="height:12.0pt" height="16"> <td class="xl162" style="height:12.0pt;font-size:9.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" height="16">07 April 2012</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">115</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">109</td> </tr> <tr style="height:12.0pt" height="16"> <td class="xl162" style="height:12.0pt;font-size:9.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" height="16">08 April 2012</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">68</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">105</td> </tr> <tr style="height:12.0pt" height="16"> <td class="xl162" style="height:12.0pt;font-size:9.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" height="16">09 April 2012</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">124</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">83</td> </tr> <tr style="height:12.0pt" height="16"> <td class="xl162" style="height:12.0pt;font-size:9.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" height="16">10 April 2012</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">160</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">23</td> </tr> <tr style="height:12.0pt" height="16"> <td class="xl162" style="height:12.0pt;font-size:9.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" height="16">11 April 2012</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">120</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">124</td> </tr> <tr style="height:12.0pt" height="16"> <td class="xl162" style="height:12.0pt;font-size:9.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" height="16">12 April 2012</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">117</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">111</td> </tr> <tr style="height:12.0pt" height="16"> <td class="xl162" style="height:12.0pt;font-size:9.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" height="16">13 April 2012</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">100</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">125</td> </tr> <tr style="height:12.0pt" height="16"> <td class="xl162" style="height:12.0pt;font-size:9.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" height="16">14 April 2012</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">100</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">97</td> </tr> </tbody></table> <table border="0" cellpadding="0" cellspacing="0" width="304"><colgroup><col style="mso-width-source:userset;mso-width-alt:4906;width:86pt" width="115"> <col style="mso-width-source:userset;mso-width-alt:2645;width:47pt" width="62"> <col style="mso-width-source:userset;mso-width-alt:5418;width:95pt" width="127"> </colgroup><tbody><tr style="height:12.0pt" height="16"> <td class="xl160" style="height:12.0pt;width:86pt; font-size:9.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border-top:.5pt solid #DCE6F1; border-right:none;border-bottom:.5pt solid #4F81BD;border-left:none; background:#DCE6F1;mso-pattern:#DCE6F1 none" height="16" width="115">May</td> <td style="width:47pt;font-size:9.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #4F81BD;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right" width="62">1922</td> <td style="width:95pt;font-size:9.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #4F81BD;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right" width="127">3797</td> </tr> <tr style="height:12.0pt" height="16"> <td class="xl161" style="height:12.0pt;font-size:9.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" height="16">01 May 2012</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">176</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">92</td> </tr> <tr style="height:12.0pt" height="16"> <td class="xl161" style="height:12.0pt;font-size:9.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" height="16">02 May 2012</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">170</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">108</td> </tr> <tr style="height:12.0pt" height="16"> <td class="xl161" style="height:12.0pt;font-size:9.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" height="16">03 May 2012</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">142</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">159</td> </tr> <tr style="height:12.0pt" height="16"> <td class="xl161" style="height:12.0pt;font-size:9.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" height="16">04 May 2012</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">124</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">113</td> </tr> <tr style="height:12.0pt" height="16"> <td class="xl161" style="height:12.0pt;font-size:9.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" height="16">05 May 2012</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">117</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">99</td> </tr> <tr style="height:12.0pt" height="16"> <td class="xl161" style="height:12.0pt;font-size:9.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" height="16">06 May 2012</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">131</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">105</td> </tr> <tr style="height:12.0pt" height="16"> <td class="xl161" style="height:12.0pt;font-size:9.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" height="16">07 May 2012</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">165</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">115</td> </tr> <tr style="height:12.0pt" height="16"> <td class="xl161" style="height:12.0pt;font-size:9.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" height="16">08 May 2012</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">165</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">68</td> </tr> <tr style="height:12.0pt" height="16"> <td class="xl161" style="height:12.0pt;font-size:9.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" height="16">09 May 2012</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">167</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">124</td> </tr> <tr style="height:12.0pt" height="16"> <td class="xl161" style="height:12.0pt;font-size:9.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" height="16">10 May 2012</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">184</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">160</td> </tr> <tr style="height:12.0pt" height="16"> <td class="xl161" style="height:12.0pt;font-size:9.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" height="16">11 May 2012</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">130</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">120</td> </tr> <tr style="height:12.0pt" height="16"> <td class="xl161" style="height:12.0pt;font-size:9.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" height="16">12 May 2012</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">118</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">117</td> </tr> <tr style="height:12.0pt" height="16"> <td class="xl161" style="height:12.0pt;font-size:9.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" height="16">13 May 2012</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">131</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">100</td> </tr> <tr style="height:12.0pt" height="16"> <td class="xl161" style="height:12.0pt;font-size:9.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" height="16">14 May 2012</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">2</td> <td style="font-size:9.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">100</td> </tr> </tbody></table>

 

[powerpivotpro]

Let's take your question about the differences between those two syntaxes first. There are two differences.

1) CALCULATE(<expr>expr, Table[Col] = Value) works great as long as you're just comparing a column to a fixed value, like 6 or "Regular"

You are correct that when you want to compare a column to something else, like another column, or to another measure, you have to use FILTER instead of just the "built-in" Column=Value filtering provided by CALCULATE.

But FILTER is oftentimes a LOT slower to evaluate than a "built-in" CALCULATE filter. So the rule of thumb is to not use FILTER to do things that can be done with built-in CALCULATE filters.

2) I've never used DATEADD() as a second argument to the && operator inside a FILTER. I saw that and just got a bit nervous that perhaps it was going to be evaluated differently there than it would be normally. I had no concrete reason for worry, just wanted to narrow down the variables.</expr>

 

[powerpivotpro]

As for your other question: why is Prev Period Sales returning the full month of April's sales?

That, I think, is because your Calendar/Dates table contains ALL of the days of May in it, even though you only have sales for half of May.

So when you navigate the calendar using DATEADD, you are doing so strictly in the Calendar table, and it's ignoring the Sales table completely during that navigation. It then selects the entire month of April, since it "came from" the entire month of May.

One solution is to have your date table always trimmed to be no more "current" than your latest sales data. That is something we do in SQL all the time, but on your desktop, that takes more manual labor.

I've solved this same problem before without trimming my date table though, let me noodle a bit before I share.

 

[masplin]

Hi Rob. Unfortunately that's the first thing I thought of but my date table finishes at 14th May. Here is the excel table with blank rows below which was a bit mystifying!

<table border="1" bordercolor="#999999" cellspacing="0"> <tbody><tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="91.5">11-May-12 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="84">2012 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="84"> 1 </td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="84"> May </td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="84"> May </td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="84">2Q </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="91.5">12-May-12 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="84">2012 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="84"> 1 </td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="84"> May </td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="84"> May </td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="84">2Q </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="91.5">13-May-12 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="84">2012 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="84"> 1 </td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="84"> May </td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="84"> May </td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="84">2Q </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="91.5">14-May-12 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="84">2012 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="84"> 1 </td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="84"> May </td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="84"> May </td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="24" valign="bottom" width="84">2Q </td></tr> <tr><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="91.5">
</td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="84">
</td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="84">
</td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="84">
</td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="84">
</td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="84">
</td></tr> <tr><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="91.5">
</td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="84">
</td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="84">
</td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="84">
</td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="84">
</td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="84">
</td></tr> <tr><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="91.5">
</td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="84">
</td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="84">
</td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="84">
</td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="84">
</td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="24" valign="bottom" width="84">
</td></tr> </tbody></table>



<table border="0" cellpadding="0" cellspacing="0" width="341"><colgroup><col style="mso-width-source:userset;mso-width-alt:2602;width:46pt" width="61"> <col style="width:42pt" span="5" width="56"> </colgroup><tbody> <tr style="height:12.0pt" height="16"> <td style="height:12.0pt" height="16">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> </tbody></table>

 

[powerpivotpro]

Well that is super puzzling. In my time intelligence workbook I am using DATEADD in exactly that situation and it's doing the right thing.

There's clearly something different in your workbook, is this the one you already sent me?

 

[masplin]

Sort of as have been doing a lot of work on it, but it doesn't have the Date Calender as hadn't appreciated I needed one when I started. I'll try and slim this one down and send it again.

Thanks Mike