Year plus one

[jelindbe]

I need to display a measure for "2010" as "2011", "2011" as "2012"....etc.

I have tried this formula (with many variations):

Sum({<Year={$(={Year}+1)} >}my_measure)

but it returns with 0. I would greatly appreciate any assistance.

[OmarBenSalem]

put as a dimension ur year; and as a measure:

below(sum(my_measure))*avg({<Year>}1)

[consultant_bi]

because you are using an aggregation to display the sum of sales or something of a year, i thought about a simple solution you can play just on the visualisation add one year for the year like my example below
and the result on two graphs below one on the left without add the +1 and the other with it

[OmarBenSalem]

stalwar1‌ ; guess from who I copied this technique? haha

[sunny_talwar]

I believe you wanted to do this Omar

below(sum({<Year>}my_measure))*avg(1)

Not sure if the OP needs Above() or Below() or After() or Before(). It will depend on the sorting and pivoting of the year dimensions.

[agigliotti]

Hi,

if you select 2010 as year you'd like to get Sum( my_measure ) of 2011 ?

[OmarBenSalem]

can't we force the aggregation to be by year ascending? directly in the expression itself?

maybe sthing like:

aggr( below(sum({<Year>}my_measure)) , (Year, ASCENDING))

?

[sunny_talwar]

We sure can

[OmarBenSalem]

If I use this :

aggr(below(sum({<Year>}Sales)) , (Year,(NUMERIC, ASCENDING)))

It does work; but when I select a Year, it correctly bring the correct values, but the other Years disappear!

How can I use this, and keep all the years even when I select a year?

[OmarBenSalem]

Found it :

sum({<Year>}aggr(below(sum({<Year>}Sales)) , (Year,(NUMERIC, ASCENDING))))