8 Replies Latest reply: Nov 20, 2015 9:22 AM by Sinan Ozdemir

# Measure as PreviousPeriod's value

Hi there, this is my first question. Sorry I haven't been able to find the answer in the forum.

Imagine, for simplicity's sake, I have 1 dimension and 1 measure with just 5 rows:

 Day Amount 1 20 2 25 3 35 4 30 5 40

I need to add a new measure that works out on the fly the amount of the previous day, so I can compare them accross the day dimension:

 Day Amount Amount_previous_day 1 20 N/A 2 25 20 3 35 25 4 30 35 5 40 30

What is the expression that allows me to do it?

(Have in mind that, for day=1, there's no previous value).

thanks a lot

• ###### Re: Measure as PreviousPeriod's value

If those are ordered just like that a simple Above() function should do the trick.

However most of the time it's not as simple as the example given. Could you please be more specific?

• ###### Re: Measure as PreviousPeriod's value

Above seems to work even when the script data is not order (correct me if I'm wrong), but it does not work well with uninformed periods.

The result should look more like this, sorry:

 Day Amount Amount_previous_day 1 20 2 25 20 3 25 4 30 5 40 30
• ###### Re: Measure as PreviousPeriod's value

Hi,

In the load script, you can do the below:

And your data model will look like this:

• ###### Re: Measure as PreviousPeriod's value

In the front end, you can also use the below expression:

Hope this helps.

• ###### Re: Measure as PreviousPeriod's value

Thanks a lot for the quick answer Sinan,

Both methods get very close to the answer I need but, as Paolo mentions, if there's no data for the previous member, it should be left empty.

It should look more like this, sorry:

 Day Amount Amount_previous_day 1 20 2 25 20 3 25 4 30 5 40 30
• ###### Re: Measure as PreviousPeriod's value

Both approaches should still work:

And here is the data model:

• ###### Re: Measure as PreviousPeriod's value

Hi again, non of these approaches are behaving as I expect. Probably my fault for not being more specific:

If I have an expression like Sum(Amount) that aggregates across all dimensions (dates, months, years, products, etc), can I define another expression that aggregates across all dimensions but for the previous period?

In my SSAS mind, this is as simple as:

([Date].currentmember.PREVMEMBER,[Measures].[Amount])

In this case I dont even need to specify whether the previous period is a day, month or year because it understands that it needs to aggregate "Amounts" at the same level that is being queried at any moment (no problem if I need to create different expressions/measures for every level).

Also, in case a date does not contain data, it can still contain previous period data (I shouldnt need to fill empty dates with zero values).

And, script data ordering does not matter because dates are implicitly ordered.

does it make sense? There has to be a simple way to show a "previous period" measure to be able to, for example, calculate increases in sales, etc.

• ###### Re: Measure as PreviousPeriod's value

You can change the expression to Above(Total Sum(Amount)), but I think it would be better if you could post a sample data-set which better reflects your issue so that we can give you the correct solutions.

Thanks