0

Im working with MS Analytics Tools. I would like to create a KPI showing monthly change of number of sold products. I have my measure [Measures].[Sold Count] and dimension [Date]. Moreover the goal should be "increased by 1.5% in selected month".

My problem is: I have no idea what to write in Status expression and Trend expression. I know that ParallelPeriod is something I should use.

I assume that Value expression can be just [Measures].[Sold Count]

Krever
  • 1,371
  • 1
  • 13
  • 32

1 Answers1

0

Try something like this:

With 
Member [Measures].[Sold Growth] as ( ( [Date].CurrentMember, [Measures].[Sold Count] ) / ( [Date].CurrentMember.Lag(1), [Measures].[Sold Count] ) - 1) *100
Member [Measures].[Sold Target] as ( [Date].CurrentMember.Lag(1), [Measures].[Sold Count] ) * 1.015

Select <a set of months> on Rows,
{ [Measure].[Sold Count], [Measures].[Sold Growth], [Measures].[Sold Target]} on Columns

From [Cube]

Remark: Using [Date].CurrentMember.Lag(1) assumes your [Date].CurrentMember is at the month level. If it's not you should probably use ParallelPeriods or another date function that can retrieve the previous month value;

nsousa
  • 4,448
  • 1
  • 10
  • 15