Max month minus 1

[rcorcoran]

Hi The expression below is giving me the max YearMonth but I cannot get it to work for the Max YearMonth -1

Any help appreciated

if(isnull(num((((Avg({$<HRPrimaryCare={7},ReferredYYYY,YearMonth={"=$(#=Max({<[HRPrimaryCare]={7}>}YearMonth))"}>}ActualValue)))), vNumber)),Null(),

num((((sum({$<HRPrimaryCare={7},ReferredYYYY,YearMonth={"=$(#=Max({<[HRPrimaryCare]={7}>}YearMonth))"}>}ActualValue)))), vNumber))

[giakoum]

What is the YearMonth format? 201606 for example?

[rcorcoran]

it is made by the text below in the script and is YYYY,MM

MonthName(Date(makedate(ReferredYYYY,ReferredMM))) as YearMonth,

[Anil_Babu_Samineni]

I recommend you to create Variable for that

For prev month you can try this too

if(isnull(num((((Avg({$<HRPrimaryCare={7},ReferredYYYY,YearMonth={"=$(#=Max({<[HRPrimaryCare]={7}>}YearMonth)-1)"}>}ActualValue)))), vNumber)),Null(),

num((((sum({$<HRPrimaryCare={7},ReferredYYYY,YearMonth={"=$(#=Max({<[HRPrimaryCare]={7}>}YearMonth)-1)"}>}ActualValue)))), vNumber))

OR

if(isnull(num((((Avg({$<HRPrimaryCare={7},ReferredYYYY,YearMonth={"=$(#=Addmonths(Max({<[HRPrimaryCare]={7}>}YearMonth)),-1)"}>}ActualValue)))), vNumber)),Null(),

num((((sum({$<HRPrimaryCare={7},ReferredYYYY,YearMonth={"=$(#=Addmonths(Max({<[HRPrimaryCare]={7}>}YearMonth)),-1)"}>}ActualValue)))), vNumber))

Note: AddMonth(Expression, -1) will gives the previous month Data. If not, better to offer you logic if you shared the application

[varshavig12]

addmonths

[sunny_talwar]

How about this:

Avg({$<HRPrimaryCare = {7}, ReferredYYYY, YearMonth = {"$(=MonthName(AddMonths(Max({<[HRPrimaryCare] = {7}>} YearMonth), -1)))"}>} ActualValue)

and for this month, you might be able to simplify your expression to this

Avg({$<HRPrimaryCare = {7}, ReferredYYYY, YearMonth = {"$(=MonthName(AddMonths(Max({<[HRPrimaryCare] = {7}>} YearMonth), 0)))"}>} ActualValue)