Set Analysis Expression for previous 3 months

[#1]

Hi all,

I want to have set analysis expression for the following scenario.

We are now in the month of April,

I have to show now previous 3 months (Jan, Feb, Mar) Bookings.

when we move to May, automatically (Feb, Mar, Apr) should be displayed.

Hardcoding (sum ({<Year={'2014'}, Month={'Jan','Feb','Mar'}[Bookings])  ) is not allowed since i am looking expression for the generation of pdf report.

i tried the following expression

  sum({<Year={'2014'},
[Date formatted]={">=$(AddMonths(YearStart($(vDate)),Month($(vDate))-4))"}* {"<=$(Date(MonthStart($(vDate))-1))"}}>}[Booking])

but i am getting YTD, which i am not looking for..

If i give "AddMonths(YearStart($(vDate)),Month($(vDate))-4))" in a text box I am getting Jan2014 and

if i give "Date(MonthStart($(vDate))-1))" am getting Mar 2014, which is what i want.

but when I combine them and give them as expression  in a pivot table as shown above, I am getting YTD

I want to display the data from Jan 2014 to Mar 2014, since we are in April.

Hope my query is clear.

Kindly help me on this issue.

Thanks in advance

Vidhya

[#2]

Hi vidhyavadkap,

You need to create 3 variables and put value in them like

VMonth1 = AddMonths(Today(),-3)

VMonth2 = AddMonths(Today(),-2)

VMonth3 = AddMonths(Today(),-1)

and use $(VMonth1) in your expression.

[mato32188]

Hi,

create 2 variables:

vPrevMonth ; month(addmonths([Date field], -1))

vPrev3Months ; month(addmonths([Date field], -3))

use it in your expression calc by <=vPrevMonth; >=vPrev3Months

hope helps

[#4]

Hi Ahmed,

I used Month(Addmonths(Today(),-3) in my expression and used 3 variables as suggested by you.

It worked and really helped me a lot.

Thanks,

Vidhya

[#5 · Accepted solution]

Thank you it would be really great if my suggestion helps you.

if your requirement are completed then please mark my reply as Correct.

[almamy_diaby]

=sum({$< YEAR ={$(=Max(YEAR)),$(=Max(YEAR-1))}, 

MONTH={ "<=$(=num(date(AddMonths(max(MONTH_YEAR),0),'MMM')))"},

MONTH_YEAR={ ">=$(=num(date(AddMonths(max(MONTH_YEAR),-3),'MMM YYYY')))"}>}  [Bookings])

*MONTH_YEAR = MonthName(DATE)