Return date from a previous month.

[Gavin23]

Hi all,

I have a couple of rows and would like to find out how to find the last date entry from a captured row. For example, below, I would like to grab the last reading date i.e. 06 Feb 2014 to display in a column in the same row as 07 Apr 2014 is displayed on. What I'm trying to achieve is calculate the days between dates to get an average on the readings. I've tried "=Previousmonth(...)", "=Calculate(...)" but really hitting a brick wall here...

Magalies View

Magalies View

Magalies View

Magalies View

<colgroup><col style="mso-width-source:userset;mso-width-alt:2673;width:56pt" width="75"> <col style="mso-width-source:userset;mso-width-alt:3328;width:70pt" width="94"> <col style="mso-width-source:userset;mso-width-alt:4551;width:96pt" width="128"> <col style="mso-width-source:userset;mso-width-alt:4977;width:105pt" width="140"> <col style="mso-width-source:userset;mso-width-alt:4664;width:98pt" width="131"> <col style="mso-width-source:userset;mso-width-alt:3441;width:73pt" width="97"> </colgroup><tbody>
[TD="class: xl68, width: 75"]Unit #
[/TD]
[TD="class: xl63, width: 94"]Complex[/TD]
[TD="class: xl66, width: 128"]Reading Date[/TD]
[TD="class: xl66, width: 140"]Reading Month[/TD]
[TD="class: xl65, width: 131"]Elec Previous[/TD]
[TD="class: xl65, width: 97"]Elec New[/TD]

[TD="class: xl69, align: right"]4[/TD]

[TD="class: xl67, align: right"]06 Feb 2014[/TD]
[TD="class: xl67"]February[/TD]
[TD="class: xl64, align: right"]84.578[/TD]
[TD="class: xl64, align: right"]85.276[/TD]

[TD="class: xl69, align: right"]4[/TD]

[TD="class: xl67, align: right"]07 Apr 2014[/TD]
[TD="class: xl67"]March[/TD]
[TD="class: xl64, align: right"]85.870[/TD]
[TD="class: xl64, align: right"]86.509[/TD]

[TD="class: xl69, align: right"]4[/TD]

[TD="class: xl67, align: right"]05 May 2014[/TD]
[TD="class: xl67"]April[/TD]
[TD="class: xl64, align: right"]86.509[/TD]
[TD="class: xl64, align: right"]87.170[/TD]

[TD="class: xl69, align: right"]4[/TD]

[TD="class: xl67, align: right"]03 Jun 2014[/TD]
[TD="class: xl67"]May[/TD]
[TD="class: xl64, align: right"]87.170[/TD]
[TD="class: xl64, align: right"]87.993
[/TD]

</tbody>

 

[scottsen]

Sorry for the slow response Gavin, busy week.

What do you need here? Like... the column works for you, but you want help understanding it better, or ?

 

[Gavin23]

Hi Scottsen,

No problem - I just basically wanted to know how to equate your formula into the spreadsheet. I'm not 100% certain about what you mena with the "&&".
I assume I'm missing something in here... =CALCULATE(MAX(Table1[Reading Date]),EARLIER(Table1[Unit #]),EARLIER(Table1[Complex]),EARLIER(Table1[Reading Date]))

 

[scottsen]

Condition1 && Condition2 is the same thing as AND(Condition1, Condition2).

The idea was to paste my formula into a new calculated column in your power pivot table (with reasonable adjustments for correct table/column names).

 

[Gavin23]

Hi Scottsen,

Needing a fresh pair of eyes please, if you could perhaps shed your expertise here...What am I missing here?
=CALCULATE(MAX(Table1[Reading Date]),EARLIER(Table1[Unit #],AND(Table1[Complex]=EARLIER(Table1[Complex],AND(Table1[Reading Date]<EARLIER(Table1[Reading Date]))))))

 

[scottsen]

Well, what you pasted is not syntactically correct, but I'm also not super sure what you are trying to do. Did you just not like &&, so you converted to AND() ?

My assumptions:
* You are using Power Pivot.
* All the data for the first able you posted... is in Power Pivot.
* You are creating a calculated column -- as opposed to measure (2010) or calculated field (2013)
* The formula, for the calculated column, that I put in a previous post... goes in the calculated column.

As far as being able to just "understand", I would start with Rob Collie's book: DAX Formulas for PowerPivot: A Simple Guide to the Excel Revolution: Rob Collie: 9781615470150: Amazon.com: Books

 

[Gavin23]

Hi Scott,

I actually purchased Bill Jelen's book "Powerpivot for the data analyst" about 3 months ago and busy working through it...I can't put it down - just blows the mind! I'm sure the two books have very similar thoughts and workings...Your assumptions are all correct. What is throwing me off is just the way you had noted the formula down...Exactly what you mentioned above with the "&&".