Previous month set analysis help please...

[#1]

Hi,

I have an expression which works fine for the selected Month to Date however I cannot ssem to get the syntax correct to replicate it for the previous Month to date:

Current working MTD expression:

sum({<[Year]={"$(=Year(Max([Date])))"},[Mnth]={"$(=Month(Max([Date])))"},[Date]={"<=$(=Date(Max([Date]), 'DD/MM/YYYY'))"},[Channel]={"Agg"},[Product]={"Motor"},[Sub-Channel]={"*"}>}[Quoted])

[Mnth] is month name eg. Jan, Feb....

I cannot seem to get it to work for the previous month to date based on the date selection and think that it is something to do with the formatting of the result into 'MMM' that I can't seem to get right.

I also have [Month] as month number (1-12)

I have tried using [Month] and applying a -1 to the formula but to no avail.

Many Thanks in anticipation of your help.

[Gysbert_Wassenaar · Accepted solution]

Perhaps like this:

sum({<[Year]=,Mnth,[Month]=,[Date]={">=$(=Date(MonthStart(Max([Date]),-1), 'DD/MM/YYYY'))<=$(=Date(AddMonths(Max([Date]),-1), 'DD/MM/YYYY'))"},[Channel]={"Agg"},[Product]={"Motor"},[Sub-Channel]={"*"}>}[Quoted])

[#3]

That's Great.

Thanks very much.