2 Replies Latest reply: Nov 23, 2015 4:08 AM by Luis Ibáñez

# Measure as PreviousPeriod's value

Hi everyone,

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 the same measure across all dimensions but for the previous period?

In my SSAS mind, this is as simple as:

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

In SSAS I dont even need to specify whether the previous period is a day, month or year because SSAS understands that it needs to aggregate "Amounts" at the same Date Level that is being queried. But it wouldnt be a problem to create a different measure for each level.

This way, if a date does not contain data, it can still contain previous period's data. For example, if there were no sales for "Bike" products in March, this month should still be shown if February (previous month) had sales.

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.

Example:

Data

 Date Product Amount 01/01/2015 Bike 3 01/01/2015 Accesories 4 01/02/2015 Bike 5 01/02/2015 Accesories 6 01/03/2015 Accesories 7 01/04/2015 Accesories 8 01/04/2015 Bike 9

Aggregated across months:

 Month Sum(Amount) Sum(Amount) PREV_MONTH 1 7 2 11 7 3 7 11 4 17 7 5 17

Aggregated across months and product:

 Month Bikes Accesories Month Sum(Amount) Sum(Amount) PREV_MONTH Sum(Amount) Sum(Amount) PREV_MONTH 1 3 4 2 5 3 6 4 3 5 7 6 4 9 8 7 5 9 8

Thanks a lot

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

Hi,

Try this ways

```LOAD *,Month(Date) as DMonth,NUM(Month(Date)) as NMonth;
Date, Product, Amount
01/01/2015, Bike, 3
01/01/2015, Accesories, 4
01/02/2015, Bike, 5
01/02/2015, Accesories, 6
01/03/2015, Accesories, 7
01/04/2015, Accesories, 8
01/04/2015, Bike, 9
];
```

Check the attached qlik file for solution.

Regards,

Anand

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

Thank you very much Anand,

Interestingly, the example seems to work fine for this pivoting table, but not for graphs (?). I added 2 charts with both measures, Sum(amount) and Above(Sum(amount)). The first chart is grouping by Product and NMonth; The second chart is grouping by NMonth and Product (the other way around). These are the results:

As you can see, the first chart for Month 3 and Bike Product is not showing "previous month sales". Instead Month 4 is showing 5 as "previous month sales".

The second chart does not show any previous month sales at all for Accesories.

Kind regards