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>

 

[DILIPandey]

Okay.. you want to display 6 Feb in same row as 07 Apr but on what criteria ?
like -> display last row's reading date where unit# = 4 AND complex = "Magalies View". Do you have such criteria as well ?

Regards,
DILIPandey

 

[Gavin23]

Hi,
Yes, that would be perfect!!

 

[DILIPandey]

Okay.. if you have this data pasted in cell a1 onwards, use below formula in G3:-

=MIN(IF(($A$2:$A3=$A3)*($B$2:$B3=$B3),$C$2:$C3,""))

enter this formula with key combination: ctrl shift enter and drag down.


Regards,
DILIPandey

 

[scottsen]

DILIPandey, you stumbled into the Power Pivot forum which has a whole different set of rules

Gavin, anything from the time intelligence functions is going to require a "real calendar table". Which is... a table of dates that has EVERY day in it (including weekends, holidays, etc). We can maybe go down that path, but I would probably think about this differently.

You are looking for the "previous reading date". Which is... the max date, that is less than the current row. I would probably do that as a calculated column.

=<br><span class="Keyword" style="color:#0070FF">CALCULATE</span><span class="Parenthesis" style="color:#D0D0D0"> (</span><br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">MAX</span><span class="Parenthesis" style="color:#D0D0D0"> (</span> MyTable[Reading Date] <span class="Parenthesis" style="color:#D0D0D0">)</span>,<br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">FILTER</span><span class="Parenthesis" style="color:#D0D0D0"> (</span><br><span class="indent8">        </span>MyTable,<br><span class="indent8">        </span>MyTable[unit] = <span class="Keyword" style="color:#0070FF">EARLIER</span><span class="Parenthesis" style="color:#D0D0D0"> (</span> MyTable[unit] <span class="Parenthesis" style="color:#D0D0D0">)</span><br><span class="indent8">        </span><span class="indent4">    </span>&& MyTable[complex] = <span class="Keyword" style="color:#0070FF">EARLIER</span><span class="Parenthesis" style="color:#D0D0D0"> (</span> MyTable[complex] <span class="Parenthesis" style="color:#D0D0D0">)</span><br><span class="indent8">        </span><span class="indent4">    </span>&& MyTable[Reading Date] < <span class="Keyword" style="color:#0070FF">EARLIER</span><span class="Parenthesis" style="color:#D0D0D0"> (</span> MyTable[Reading Date] <span class="Parenthesis" style="color:#D0D0D0">)</span><br><span class="indent4">    </span><span class="Parenthesis" style="color:#D0D0D0">)</span><br><span class="Parenthesis" style="color:#D0D0D0">)</span><br>

 

[DILIPandey]

Oops.. yes... you are right Scottsen.

I did'nt noticed that it is powerpivot section.


Regards,
DILIPandey

 

[Gavin23]

Hi Scottsen,

Thanks for the reply - i think this is possibly what I'm looking for but I'm not 100% sure on how to insert this as a calculated field. Is this the VBA way of the formula ?

 

[Gavin23]

Thanks DILIPandey...was going to comment but Scottsen beat me to it....Thanks anyway for the reply

 

[Gavin23]

I'm just not sure how to read your formula if I try and insert it into the calculated column...
=CALCULATE(MAX(Table1[Reading Date]),EARLIER(Table1[Unit #]),EARLIER(Table1[Complex]),EARLIER(Table1[Reading Date]))

 

[Gavin23]

Can anyone perhaps assist on this one please ? Just not sure how to read this calculated column....
=
CALCULATE (
MAX ( MyTable[Reading Date] ),
FILTER (
MyTable,
MyTable[unit] = EARLIER ( MyTable[unit] )
&& MyTable[complex] = EARLIER ( MyTable[complex] )
&& MyTable[Reading Date] < EARLIER ( MyTable[Reading Date] )
)
)