Previous Months

[kevbrown]

I've been asked to produce a report that shows current month, last month and the month previous to that.  The way they'd like this to work is by having buttons for this month, last month and the month previous to that.  I can get this month to work "select" Max(Month) but how do i get the previous 2 months? Max(Month)-1 doesn't work

[raghvendrasingh]

Hi Kevin,

Can you please share your application, in which its not working. Because in text box i checked that it is working fine.

Please share the exact requirement what you exactly want?

Regards

Raghvendra

[rubenmarin · Accepted solution]

Hi Kevin, Max can have a parameter to get the 2nd Max value:

Max(Month, 2)

But when talking about something related to dates usually is better to use functions related to dates:

AddMonths(Max(Date), -2) // Will return a date two months earlier for the max date

If Max(Date) is '01/02/2016' it will return '01/12/2015'. So Month(AddMonths(Max(Date), -2)) will return December, wich is two months before the max possible date.

[Anonymous]

you create a variable e.g. vSelectedMonth

button for this month you define Action Set Variable vSelectedMonth = max(Month)

button for last month you define Action Set Variable vSelectedMonth = date(addmonths(max(Month),-1))

button for month previous you define Action Set Variable vSelectedMonth = date(addmonths(max(Month),-2))

-> Month must be defined as datefield (not as text)

in your table etc. you create Expression: sum({<Month={'$(=vSelectedMonth)'}>}Value

[Anonymous]

Hi

Date(Addmonths(max(Date),-1),'DD/MM/YYYY') for previous month 

Date(Addmonths(max(Date),-2),'DD/MM/YYYY') for two months ago

But if you are doing sum aggregate functions like sum or count you need to work on set analysis

like this ....

Sum({<Date = {"$(='>=' & Date(MonthStart(Max(Date)), 'DateFieldFormatHere') & '<=' &Date(MonthEnd(Max(Date)), 'DateFieldFormatHere'))"} >}Sales)