3 Replies Latest reply: Sep 27, 2017 9:54 AM by Sunny Talwar

# value difference between random months

Hi,

hope you can help in the below.

I need to find turnover deviations for every month in comparison with previous month.

The following expression works for one option - last month versus second to last month.

Sum({<[Posting date.autoCalendar.Date]

={'>=\$(=AddMonths( MonthStart (Today()),-1))<=\$(=AddMonths( MonthEnd (Today()),-1))'}>} Turnover)

-(Sum({<[Posting date.autoCalendar.Date]

={'>=\$(=AddMonths( MonthStart (Today()),-2))<=\$(=AddMonths( MonthEnd (Today()),-2))'}>} Turnover)),

How to modify the expression to make it working for random months selection, e.g.:

- select June to get the difference between June and May,

- select March to get the difference between March - February, etc

Appreciate any advice

Many thanks

Jacek

• ###### Re: value difference between random months

May be this

Sum({<[Posting date.autoCalendar.Date]

={'>=\$(=AddMonths( MonthStart (Max([Posting date.autoCalendar.Date])),-1))<=\$(=AddMonths( MonthEnd (Max([Posting date.autoCalendar.Date])),-1))'}>} Turnover)

-(Sum({<[Posting date.autoCalendar.Date]

={'>=\$(=AddMonths( MonthStart (Max([Posting date.autoCalendar.Date])),-2))<=\$(=AddMonths( MonthEnd (Max([Posting date.autoCalendar.Date])),-2))'}>} Turnover)),

• ###### Re: value difference between random months

Thank you for quick reply Sunny, but it doesn't work, the expression shows null.

• ###### Re: value difference between random months

How about this

Sum({<[Posting date.autoCalendar.Date]

={'>=\$(=AddMonths( MonthStart (Max([Posting date.autoCalendar.Date])),-1))<=\$(=AddMonths( MonthEnd (Max([Posting date.autoCalendar.Date])),-1))'}, Month, Year>} Turnover)

-(Sum({<[Posting date.autoCalendar.Date]

={'>=\$(=AddMonths( MonthStart (Max([Posting date.autoCalendar.Date])),-2))<=\$(=AddMonths( MonthEnd (Max([Posting date.autoCalendar.Date])),-2))'}, Month, Year>} Turnover))