Comparison Month <-> previous month

[#1]

Hi all,

I tried to find a solution within the community group here but didn't find any solution which works for me.

I have a table like following:

MyDate           Value     Type

01.10.2012     10.000     Car

01.10.2012         500     Bike

01.11.2012     12.000     Car

01.11.2012       1.200     Mini

01.11.2012          600     Bike

I want to have a straight table with date in the Dimension MyDate and the expressions value, prev.month and Difference in the lines.

                         Oct 2012          Nov 2012      ....

Value                   10.500               13.800     .....

prev.Month               0                   10.500     ....

Difference            10.500                 3.800     .....

I created a new Variable vMonthMinus1 like this: =date(addmonths(MyDate,-1),'YYYY-MM')

In my expression I tried to have the following:

=sum({<MyDate=vMonthMinus1>}[Value])

Unfortunetly I got only 0 as result. Can someone help me ?

Many thanks in advance from germany

Markus

[Gysbert_Wassenaar · Accepted solution]

You're using Month as a dimension and then set analysis expressions won't help you. The set is calculated once for the entire chart, not per dimension value. You could try using the before() function.

Value: sum(Value)

prev.Month: before(sum(Value)

Difference: Value - [prev.Month]

The disadvantage is that if you select only Nov 2012 you won't get a result for prev.Month since Oct 2012 isn't in your chart anymore.

[alexandros17]

add a new column in the table where you compute previous month then left join this table with the same table on previous month of the first table = month of the second one.

this adds value columns for previous month ...

Hope it helps

[alexandros17]

Sorry Gysbert, I did not see your answer (exact as usual)

Alexandros17

[#5]

Hi Gysbert,

great - it works. Just so simple and I didn't "saw" it.

Best regards

Markus