Prior Year Max Month -1

[#1]

Hi,

I want to get the value of max date of month of prior year -1.

Max Date in my qvw is 10/11/2016. Field name for date is Trans_Date

So, I want to get 9/30/2015

I gave my expression as =Date(monthend(addyears(Max(Trans_Date),-1))) this is returning 10/31/2015. I want to get 9/30/2015.

Any help greatly appreciated.

[sunny_talwar]

May be try this:

=Date(MonthStart(AddYears(Max(Trans_Date),-1))-1)

[maxgro]

=MonthEnd(AddMonths(Max(Trans_Date),-13))

[#4]

Thanks for the quick reply Sunny. It works

Just a quick qn, Now how about if I want to get 8/31/2015

Because my expression is KPI(Completed Month)-KPI(Completed Month -1) --- Current Year

For Prior Year Same expression but for Year -1.

So I have

vToDateCompletedMonth = Date(Monthend(Max(Link_Date),-1))   (Result - 9/30/2016)

vToDateCompletedPriorMonth = Date(Monthend(Max(Link_Date),-2))   (Result - 8/31/2016)

vToDateCompleteMonthPriorYear =Date(monthstart(addyears(Max(Link_Date),-1))-1)   (Result - 9/30/2015)

vToDateCompletePriorMonthPriorYear ?              (Expected result - 8/31/2015)

Hope my question makes sense..

[#5]

Thanks for the quick reply Maximo. But the expression won't work in all cases.

[sunny_talwar · Accepted solution]

May be this:

Date(monthstart(addyears(Max(Link_Date),-1), -1)-1)

[johnw]

What cases? I do see one problem, which is that monthend() returns a timestamp rather than a date, but that can be fixed with a daystart(), and I doubt that's what you meant. I ran decades of possible dates through it, and I get the same results for that fixed expression, and Sunny's expression, and this:

monthstart(max(Trans_Date),-12)-1