Previous Month Expression

[#1]

Hi,

Please can someone help me with calculating the previous month expression.  I have a straight table with number of licenses calculated by:

sum([Total Licenses])

Against this I want to show the same value for the previous month, so I written the below expression:

=sum({$<[Month Key]={$(=[PM Key])}>} [Total Licenses])

[Month Key] is held on every record to show the month e.g. 3_2012. 

[PM Key] is also held on every record to show the previous month e.g. 2_2012.

but not working :-<

Grateful for any assitance with this.

Mike.

[manideep78]

Sum({<[PM Key]>}[Total Licenses])

[#3]

Thanks Manideep but this just gives the same total as the current month value.

[#4]

hope it works

sum({<Month ={$(=Max(Month)-1)}>}[Total Licenses])

[#5]

Sorry but no joy with that either.  Can't do a "month -1" because my month is a string.  Even with integers this would not deal with January correctly.

[manideep78]

Try this

Sum({<[Month Key]=[PM Key]>}[Total Licenses])

Hope it helps

[#7]

Thanks but still no joy.  I think that expression would look for [Total Licenses] where [Month Key] = "PM Key"... this will never be true.  I think it needs to be where the value of [PM Key] = [Month Key].

[manideep78]

What does your [Month Key] represents? what is the actual data int that field? Does your [PM Key] represents only your previous month data???

[#9]

Let me give an example:

[Month Key]     [Total Licenses]     [PM Key]

12_2012          95                          11_2012

1_2013            100                        12_2012

2_2013            105                        1_2013

3_2013            120                         2_2013

.. this should then be displayed in a straight table as...

Month          [Total Licenses]     [Last Month]

Dec-2012     95                          -

Jan-2013     100                         95

Feb-2013     105                        100

Mar-2013     120                         105

[#10]

Hi Michael,

try the above() function, eg above([Total Licences]).