ssas - Moving Average of Last 24 months -

i have calculated member calculates moving average last 12 months:

iif(isempty(sum({[time].[month].currentmember:null},  [measures].[count])), null, avg (    [time].[month].currentmember.lag(11) :     [time].[month].currentmember,     [measures].[count] )) 

the iif condition in place because don't want values future months (with no value), without it.

what want have measure last 24 months since last not empty month.

i've tried tail , lag no luck (i post attempts here after many tries deleted them , not know begin again).

thanks @whytheq final solution used:

create dynamic set currentcube.[firstemptymonth]  { tail       (         nonempty         (           [time].[month].members          ,[measures].[count]         )        ,1       ).item(0).nextmember };         create dynamic set currentcube.[monthstoignore]  {[firstemptymonth].item(0) : null}     +        {null : [firstemptymonth].item(0).lag(25)} ;                 create member currentcube.[measures].[moving average]  iif     (       intersect({[time].[month].currentmember},[monthstoignore]).count = 1      ,null      ,avg   (     [time].[month].currentmember.lag(11) : [time].[month].currentmember    ,[measures].[count]   )     ); 

in advwrks i've got this:

with    set [futuremonthswithnodata]      {         tail         (           nonempty           (             [date].[calendar].[month].members            ,[measures].[internet sales amount]           )          ,1         ).item(0).nextmember       : null     }    member [measures].[blah]      iif     (         intersect         (           {[date].[calendar].currentmember}          ,[futuremonthswithnodata]         ).count       = 1      ,null      ,1     )  select    {     [measures].[internet sales amount]    ,[measures].[blah]   } on 0  ,[date].[calendar].[month].members on 1 [adventure works]; 

it returns this:

so saying create initial set of futuredateswithnodata , use set create condition within script. set (i think) in cube:

set [futuremonthswithnodata]  {     tail     (       nonempty       (         [time].[month].[month].members        ,[measures].[count]       )      ,1     ).item(0).nextmember   : null }  

your measure follows:

iif (     intersect     (       {[time].[month].currentmember}      ,[futuremonthswithnodata]     ).count   = 1  ,null  ,avg   (     [time].[month].currentmember.lag(11) : [time].[month].currentmember    ,[measures].[count]   ) ) 

if want exclude months prior 24 months ago script sums logic:

with    set [fistemptymonth]      {       tail       (         nonempty         (           [date].[calendar].[month].members          ,[measures].[internet sales amount]         )        ,1       ).item(0).nextmember     }    set [monthstoignore]        {[fistemptymonth].item(0) : null}     +        {null : [fistemptymonth].item(0).lag(24)}    member [measures].[blah]      iif     (       intersect({[date].[calendar].currentmember},[monthstoignore]).count = 1      ,null      ,1     )  select    {[measures].[internet sales amount]} on 0  ,[date].[calendar].[month].members on 1 [adventure works];