KPI - Sales Trend

Hi,

I am trying to create a Sales Trend KPI, where the value expression is last month sales (Identify last month based on current system date) and target expression is last month previous year sales amount times 1.04.

Is there a way to accomplish this using MDX in KPI.

Thanks,

Ravi

Identifying the lastest month of data is the trickiest part. One technique to do this is to create a calculated member named CurrentMonth that uses the VBA!Date() and VBA!DatePart() to construct a member reference that can then be resolved with StrToMember. Another techinique is to again create a CurrentMonth calculation with a hard coded reference to a date and then update the definition of this calculation each time a new month of data is loaded. (See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvbadev/html/pullingpiecesapart.asp for some help on the VBA functions.)

Once you have this CurrentMonth member, you can use the ParallelPeriod MDX function (http://msdn2.microsoft.com/en-us/library/ms145500(SQL.90).aspx) to calculate the lat month of the previous year.

|||Thanks Matt.