Previous months value not getting correctly

[ashis]

Hi ,

In my data model, I have a field in my master calendar call Month Counter , it is define as

Year(Date) * 12 + num(Month(Date)) as [Month Counter] (combination of Year and Month)

I have created variable PreviousMonth as

=max([Month Counter]) -1

Now if I want to get average of xxx of the previous month I put it in the expression as

=avg({1<[Month Counter]={'$(vPreviousMonth)' }>} xxx) 

This is giving me wrong result , if I write the expression as

=avg({<[Month Counter]={'$(vPreviousMonth)' }>} xxx)  , I get null value.

Just to test my month counter I took two text boxes and put expression as

current month = =max([Month Counter])

previous month ==max([Month Counter]) -1 it gives correct output though.

Any idea where I am doing wrong.

[arjakumar]

Hi Ashis,

Please provide the sample values.

Thanks

[ashis]

For let say if I select Mar 18 and current value is  130 then

Feb'18 as 128

the Previous month =avg({1<[Month Counter]={'$(vPreviousMonth)' }>} xxx)  expression is giving some different value than 128 which is incorrect.

[sunny_talwar]

How about one of these?

=Avg({1<[Month Counter] = {'$(=$(vPreviousMonth))'}>} xxx)

or this

=Avg({1<[Month Counter] = {'$(=vPreviousMonth)'}>} xxx)

[arjakumar]

Please take out the single quotes ('') and try

=Avg({1<[Month Counter] = {$(=vPreviousMonth)}>} xxx)

[sunny_talwar]

Why do single quotes need to be taken out?

[ashis]

Hi Sunny,

It is same .

For current month Avg I am writing as

current month= avg({<[Month Counter]={'$(=MAX([Month Counter]))'}>} xxx)

for previous month I am writing as

Previous month =avg({1<[Month Counter]={'$(vPreviousMonth)'}>} xxx)

now when I choose current month as march it gives me current value 126.3933  and for FEB 126.76706

But when I choose month as feb the current value is 125.9241 instead of 126.76706

In this case the data is not matching .

[Chanty4u]

try this

=Avg({1<[Month Counter] = {"=$(=$(vPreviousMonth))"}>} xxx)

[sunny_talwar]

Is there a sample you would be able to share to look at this issue?

[ashis]

Same result.