First, Last, Middle ??

I have a Matrix that always displays two rows of data.
One row show values from Jan 1 of the current year.
The second row shows values for today.
In the foot I show the % the values have changed so YTD with the following
formula:
=(Last(Fields!Core.Value) - First(Fields!Core.Value)) /
First(Fields!Core.Value) * 100
This works great and looks like this:
Date | Core
--
Jan 1 2005 | $400
Aug 31 2005 | $500
--
Footer +25%
I have been asked to add in the value from a year ago today but still
display the change in value from just Jan 1.
Date | Core
--
Aug 31 2004 | $300
Jan 1 2005 | $400
Aug 31 2005 | $500
--
Footer +25% (Diff between Jan 1 and Aug 31 2005)
How would I calc the % change in the footer. My formula will not work as the
"First" value is a year ago today not Jan 1. Is there a "Middle" function ?
:) :)
Thoughts ?
Thanks in Advance
Pete MitchellTry something like this:
=(Last(Fields!Core.Value) - CDate("1/1/"&CStr(Year(First(Fields!Core.Value)))))
/
CDate("1/1/"&CStr(Year(First(Fields!Core.Value)))) * 100
GeoSynch
"PeteMitchell" <PeteMitchell@.discussions.microsoft.com> wrote in message
news:EE7A94F6-8F3B-46EC-87E3-7DA323C927D0@.microsoft.com...
>I have a Matrix that always displays two rows of data.
> One row show values from Jan 1 of the current year.
> The second row shows values for today.
> In the foot I show the % the values have changed so YTD with the following
> formula:
> =(Last(Fields!Core.Value) - First(Fields!Core.Value)) /
> First(Fields!Core.Value) * 100
> This works great and looks like this:
> Date | Core
> --
> Jan 1 2005 | $400
> Aug 31 2005 | $500
> --
> Footer +25%
> I have been asked to add in the value from a year ago today but still
> display the change in value from just Jan 1.
> Date | Core
> --
> Aug 31 2004 | $300
> Jan 1 2005 | $400
> Aug 31 2005 | $500
> --
> Footer +25% (Diff between Jan 1 and Aug 31 2005)
> How would I calc the % change in the footer. My formula will not work as the
> "First" value is a year ago today not Jan 1. Is there a "Middle" function ?
> :) :)
> Thoughts ?
> Thanks in Advance
> Pete Mitchell|||Actually, it probably shoud be:
=(Last(Fields!Core.Value) - CDate("1/1/"&CStr(Year(Last(Fields!Core.Value)))))
/ CDate("1/1/"&CStr(Year(Last(Fields!Core.Value)))) * 100
GeoSynch
"GeoSynch" <SpamSlayed@.Casablanca.com> wrote in message
news:ecLK3yprFHA.3884@.TK2MSFTNGP11.phx.gbl...
> Try something like this:
> =(Last(Fields!Core.Value) -
> CDate("1/1/"&CStr(Year(First(Fields!Core.Value))))) /
> CDate("1/1/"&CStr(Year(First(Fields!Core.Value)))) * 100
>
> GeoSynch
>
> "PeteMitchell" <PeteMitchell@.discussions.microsoft.com> wrote in message
> news:EE7A94F6-8F3B-46EC-87E3-7DA323C927D0@.microsoft.com...
>>I have a Matrix that always displays two rows of data.
>> One row show values from Jan 1 of the current year.
>> The second row shows values for today.
>> In the foot I show the % the values have changed so YTD with the following
>> formula:
>> =(Last(Fields!Core.Value) - First(Fields!Core.Value)) /
>> First(Fields!Core.Value) * 100
>> This works great and looks like this:
>> Date | Core
>> --
>> Jan 1 2005 | $400
>> Aug 31 2005 | $500
>> --
>> Footer +25%
>> I have been asked to add in the value from a year ago today but still
>> display the change in value from just Jan 1.
>> Date | Core
>> --
>> Aug 31 2004 | $300
>> Jan 1 2005 | $400
>> Aug 31 2005 | $500
>> --
>> Footer +25% (Diff between Jan 1 and Aug 31 2005)
>> How would I calc the % change in the footer. My formula will not work as the
>> "First" value is a year ago today not Jan 1. Is there a "Middle" function ?
>> :) :)
>> Thoughts ?
>> Thanks in Advance
>> Pete Mitchell
>|||Thanks a bunch.
How does that work ?
There are two fields in play here : Date and Core
How is that get the Core.value when the Date.value = Jan 1 2005 ?
Pete
"GeoSynch" wrote:
> Actually, it probably shoud be:
> =(Last(Fields!Core.Value) - CDate("1/1/"&CStr(Year(Last(Fields!Core.Value)))))
> / CDate("1/1/"&CStr(Year(Last(Fields!Core.Value)))) * 100
>
> GeoSynch
>
> "GeoSynch" <SpamSlayed@.Casablanca.com> wrote in message
> news:ecLK3yprFHA.3884@.TK2MSFTNGP11.phx.gbl...
> > Try something like this:
> > =(Last(Fields!Core.Value) -
> > CDate("1/1/"&CStr(Year(First(Fields!Core.Value))))) /
> > CDate("1/1/"&CStr(Year(First(Fields!Core.Value)))) * 100
> >
> >
> > GeoSynch
> >
> >
> > "PeteMitchell" <PeteMitchell@.discussions.microsoft.com> wrote in message
> > news:EE7A94F6-8F3B-46EC-87E3-7DA323C927D0@.microsoft.com...
> >>I have a Matrix that always displays two rows of data.
> >> One row show values from Jan 1 of the current year.
> >> The second row shows values for today.
> >>
> >> In the foot I show the % the values have changed so YTD with the following
> >> formula:
> >>
> >> =(Last(Fields!Core.Value) - First(Fields!Core.Value)) /
> >> First(Fields!Core.Value) * 100
> >>
> >> This works great and looks like this:
> >> Date | Core
> >> --
> >> Jan 1 2005 | $400
> >> Aug 31 2005 | $500
> >> --
> >> Footer +25%
> >>
> >> I have been asked to add in the value from a year ago today but still
> >> display the change in value from just Jan 1.
> >>
> >> Date | Core
> >> --
> >> Aug 31 2004 | $300
> >> Jan 1 2005 | $400
> >> Aug 31 2005 | $500
> >> --
> >> Footer +25% (Diff between Jan 1 and Aug 31 2005)
> >>
> >> How would I calc the % change in the footer. My formula will not work as the
> >> "First" value is a year ago today not Jan 1. Is there a "Middle" function ?
> >> :) :)
> >>
> >> Thoughts ?
> >>
> >> Thanks in Advance
> >>
> >> Pete Mitchell
> >
> >
>
>|||CDate("1/1/"&CStr(Year(Last(Fields!Core.Value)))) evaluates thusly:
Last(Fields!Core.Value) = '08/31/2005' data type Date
Year(Last(Fields!Core.Value)) = '2005' data type Integer
CStr(Year(Last(Fields!Core.Value))) converts it to a string value
so that when concatenated with "1/1/" it will evaluate to string value
"1/1/2005"
CDate converts it back to an actual date value of '01/01/2005'
GeoSynch
"PeteMitchell" <PeteMitchell@.discussions.microsoft.com> wrote in message
news:940A01C6-D8A5-4C44-8BA4-3AF232AD790F@.microsoft.com...
> Thanks a bunch.
> How does that work ?
> There are two fields in play here : Date and Core
> How is that get the Core.value when the Date.value = Jan 1 2005 ?
> Pete
>
> "GeoSynch" wrote:
>> Actually, it probably shoud be:
>> =(Last(Fields!Core.Value) -
>> CDate("1/1/"&CStr(Year(Last(Fields!Core.Value)))))
>> / CDate("1/1/"&CStr(Year(Last(Fields!Core.Value)))) * 100
>>
>> GeoSynch
>>
>> "GeoSynch" <SpamSlayed@.Casablanca.com> wrote in message
>> news:ecLK3yprFHA.3884@.TK2MSFTNGP11.phx.gbl...
>> > Try something like this:
>> > =(Last(Fields!Core.Value) -
>> > CDate("1/1/"&CStr(Year(First(Fields!Core.Value))))) /
>> > CDate("1/1/"&CStr(Year(First(Fields!Core.Value)))) * 100
>> >
>> >
>> > GeoSynch
>> >
>> >
>> > "PeteMitchell" <PeteMitchell@.discussions.microsoft.com> wrote in message
>> > news:EE7A94F6-8F3B-46EC-87E3-7DA323C927D0@.microsoft.com...
>> >>I have a Matrix that always displays two rows of data.
>> >> One row show values from Jan 1 of the current year.
>> >> The second row shows values for today.
>> >>
>> >> In the foot I show the % the values have changed so YTD with the following
>> >> formula:
>> >>
>> >> =(Last(Fields!Core.Value) - First(Fields!Core.Value)) /
>> >> First(Fields!Core.Value) * 100
>> >>
>> >> This works great and looks like this:
>> >> Date | Core
>> >> --
>> >> Jan 1 2005 | $400
>> >> Aug 31 2005 | $500
>> >> --
>> >> Footer +25%
>> >>
>> >> I have been asked to add in the value from a year ago today but still
>> >> display the change in value from just Jan 1.
>> >>
>> >> Date | Core
>> >> --
>> >> Aug 31 2004 | $300
>> >> Jan 1 2005 | $400
>> >> Aug 31 2005 | $500
>> >> --
>> >> Footer +25% (Diff between Jan 1 and Aug 31 2005)
>> >>
>> >> How would I calc the % change in the footer. My formula will not work as
>> >> the
>> >> "First" value is a year ago today not Jan 1. Is there a "Middle" function
>> >> ?
>> >> :) :)
>> >>
>> >> Thoughts ?
>> >>
>> >> Thanks in Advance
>> >>
>> >> Pete Mitchell
>> >
>> >
>>