Set Analysis expression for previous month details

[#1]

Hello

I converted below pivot table to a straight table  and next to the closing stock, i want to add up closing stock for previous month (i.e. if the diagram shows Feb 2014 figures, i want a new field 'Closing stock for previous month' reflecting data for Jan 2014 for which i gave expression as

Sum({<Year={"=$(=max(Year))"},Month_Name=,Month={"<=$(=Max(Month)-1)"}>}[Closing Stock(Unit)])

It gives me incorrect result, how can it be achieved?

Thanks

[Anonymous]

try with

=sum({<Month_Name={"$(AddMonths(Month,-1))"}>}  [Closing Stock(Unit)])

Marc.

[#3]

doesnt give any result

[Anonymous]

Could you please attach a sample file?

Marc.

[PrashantSangle]

Hi,

As you say you want Closing stock of Previous Month then

Month={"<=$(=Max(Month)-1)"}

Condition wont work for you

Since you have to write

Month={"$(=Max(Month)-1)"}

then your expression became

Sum({<Year={"=$(=max(Year))"},Month_Name=,Month={"$(=Max(Month)-1)"}>}[Closing Stock(Unit)])

Regards

[Anonymous]

Add an equal sign:

=sum({<Month_Name={"$(     =     AddMonths(Month,-1))"}>}  [Closing Stock(Unit)])

Marc.

[#7]

Thanks, but i have already used it, it doesnt give me correct figures

[jyothish8807]

Hi,

Try this:

Sum({<Year=,Month_Name=,Month={"$(=Date((Max(Month)-1),'MM-YY')"}>}[Closing Stock(Unit)])

Regards

KC