Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)),
Thank you for quick reply Sunny, but it doesn't work, the expression shows null.
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))