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.

[ashis]

Hi Rakesh,

I understand why I am getting wrong value for previous month.

Because I am using this 1 in the set analysis so it is considering all the record set instead of my current selection.

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

current month = =max([Month Counter])

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

However If I remove 1 from the set analysis and modify the expression as

=avg({<[Month Counter]={'$(vPreviousMonth)' }>} xxx)  , then I get same value as I get for current month that is

=avg({<[Month Counter]={'=max([Month Counter]) '}>} ReportTimeInSeconds)

Do you have any idea why it is giving the same value or anything i am missing here.

[ashis]

Hi Sasidhar,

if I use avg({<[Month Counter]={'$(=MAX([Month Counter])-1)'}>} xxx) as expression then I get

null value(-).

I am not sure why it is showing null, though i have data.

[ashis]

Hi Sasidhar,

Sorry please ignore my previous message , I do get value using your expression however it is the same as current months value.

[sunny_talwar]

Try this

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

[ashis · Accepted solution]

got to work using this

=avg({<[Month Counter] = {"$(=Max([Month Counter]) - 1)"},Month,Year>} xxx)

Thank you so much for your inputs.

[sunny_talwar]

Awesome, please close the thread by marking your answer as correct and any helpful responses.

Best,

Sunny