Pick Last Year Data

[#1]

Hi Guys,

I have created below data in Pivot. In Growth Column What I want is Pick the value of last year which I will use in Growth Calculation.

e.g. in Sep-2014 the value should be 10820 and oct 2014 value should be 14763 and so on...

as of now I am try to find it using addyears function. Which is not giving the desire result. Please help on this.

=sum({<Period={$(=AddYears(Period,-1))}>} Sales)

	Product1	Product1	Product1
Period	Sum of Sales	Market Share	Growth
Sep-2013	10820	2.3%	0
Oct-2013	14763	2.5%	0
Nov-2013	16591	3.3%	0
Dec-2013	17578	3.3%	0
Jan-2014	20318	3.4%	0
Feb-2014	16436	3.3%	0
Mar-2014	15745	3.0%	0
Apr-2014	14627	3.0%	0
May-2014	15374	3.1%	0
Jun-2014	10996	2.4%	0
Jul-2014	11400	2.3%	0
Aug-2014	8446	2.0%	0
Sep-2014	8524	1.7%	0
Oct-2014	15986	3.0%	0
Nov-2014	14872	3.0%	0
Dec-2014	13876	2.7%	0
Jan-2015	21910	3.3%	0
Feb-2015	20817	3.8%	0
Mar-2015	19031	3.3%	0
Apr-2015	13032	2.5%	0
May-2015	10769	2.1%	0
Jun-2015	9684	2.0%	0
Jul-2015	9438	1.8%	0
Aug-2015	8525	1.8%	0

[PrashantSangle]

Hi,

Your expression is correct.

just need to use " "

Try like

=sum({<Period={"$(=AddYears(Period,-1))"}>} Sales)

Also confirm Period is in date format or not.

Regards

[senpradip007]

Try like =sum({<Period={$(=date(AddYears(Period,-1), 'MMM-YYYY'))}>} Sales)

[#4]

Hi Pradip,

it's not working. giving the same 0 result.

[SreeniJD]

Hi Maksood

Try this,

sum({<Year={'$(=vPYear)'},Period={'=$(=vMaxMonth)'}>}[Sum of Sales])

HTH,

Sreeni

[PrashantSangle]

[Anonymous]

if the period values are in text format then you need to explicitly convert it into date by using Date# function .

Hope this would work

[#8]

Hi Max,

Yes Period is in date format. still not working after applying "".

[#9]

Hi Max,

Yes Period is in date format. still not working after applying "".

[#10]

Hi Sreeni,

do I need to create vPYear and vMaxMonth? What value should I store in these vairbles?