Comparison with Same Period Last Year

[CurroCa]

Hi dears,

I need to show in the same table some metrics and theirs values in the same period in the last year. I have created a calendar table with the dates in the format DD/MM/YYYY also a year field (Year(Date) as YEAR) but no more date fields by the moment.

I´m trying to calculate one metric with this expression, but it doesn´t work:

Sum({<YEAR = {$(=YEAR-1)}>}[Sales])

However the debug or IDE at the bottom, it tell me it´s right and evaluate the expression so: Sum({<YEAR = {2011}>}[Sales]) , but in the table shows zero.

If I directly use YEAR = 2011 of course the result is correct but I need the calculation to be automatic for all the values of the date and years.

Please, what am I doing wrongly or how can I solve my problem?

Thanks in advance

[G3S]

does doing the year calculation in the set analysis work?

year(yourdatefield)-1

[sidhiq91]

@CurroCa  Please see the code that I first used in the back end:

NoConcatenate
Temp:
Load Date(Date,'YYYY') as Year,
Sales,
Date;

Load Date(Date#(Date,'DD/MM/YYYY'),'DD/MM/YYYY') as Date,
Sales

inline [
Date, Sales
01/01/2012, 20
01/02/2012, 30
01/03/2012, 40
01/01/2011, 20
01/02/2011, 20
01/03/2011, 20
];

Exit Script;

Front End Expression: Sum({<Year={"$(=Date(Addyears(Max(Date),-1),'YYYY'))"}>}Sales)

If this resolves your issue, please like and accept it as a solution.

[MayilVahanan]

Hi

Try like below

Sum({<YEAR = {$(=Max(YEAR)-1)}>}[Sales])

[CurroCa]

I´ll review in detail your proposal. Thanks a lot.

[CurroCa]

Yes, I doing it the set analysis. 

[CurroCa]

with the advice of @MayilVahanan things go better, but now I have other issues. I´ll explain it.

These are my data:

I need something like this:

I get that result using this expression for Sum_Max(Year)-1 column and it´s right:

But I have problems when I filter by dates. Filtering for 28/12/2013 and 29/12/2013 y get this:

And Sum_Max(YEAR)-1 must be 62 as in the upper table.

¿What have I to apply in the expression to calculate it independently of the other columns? I need the Max(Year)-1 to be to be calculated for all the rows of DIMENSION, so Max(Year)-1 = 2012, but the expresion is not doing it.

[MayilVahanan · Accepted solution]

Hi

If you want to exclude the dates in the expression, try like below

Sum({<YEAR = {$(=Max(YEAR)-1)}, DateFiled=>}[Sales])

[CurroCa]

It seems that goes on. I'll do some tests more and will give you my feedback.

Thanks a lot. 

[CurroCa]

thanks @MayilVahanan @sidhiq91  and @G3S  for your help.

Very appreciated.