Ignore first year data in YoY calculation

[Faiz]

Hi,

I want to show Year on Year growth through a graph. The data in the table is shown below.

I drew the below graph using the formula: 

Aggr(Sum(SALES)/Above(Sum(SALES)) - 1, ([Date.autoCalendar.Year], (NUMERIC)))*100

1) How can I start the graph from 2014 instead of 2013 since the data for 2013 is not correct?

2) How can I make 2013 data as zero instead of -99.59?

[rubenmarin]

Hi, you can try with set analysis to fix selected years:

Aggr(Sum({<[Date.autoCalendar.Year]={">2013"}>} SALES)/Above(Sum({<[Date.autoCalendar.Year]={">2013"}>} SALES)) - 1, ([Date.autoCalendar.Year], (NUMERIC)))*100

Or using *= so year selections still filters data:

Aggr(Sum({<[Date.autoCalendar.Year]*={">2013"}>} SALES)/Above(Sum({<[Date.autoCalendar.Year]*={">2013"}>} SALES)) - 1, ([Date.autoCalendar.Year], (NUMERIC)))*100

To make 2013 as 0 I think the easy and simplest way is to use an if:

IF([Date.autoCalendar.Year]=2013, 0, Aggr(Sum({<[Date.autoCalendar.Year]*={">2013"}>} SALES)/Above(Sum({<[Date.autoCalendar.Year]*={">2013"}>} SALES)) - 1, ([Date.autoCalendar.Year], (NUMERIC)))*100)