ssas - Previous Equivalent MTD Across Multiple Months -

ssas - Previous Equivalent MTD Across Multiple Months -

if today's date 11 july 2008 mtd sum range {01 july 2008 - 11 july 2008}. previous equivalent mtd period in june {01 june 2008 - 11 june 2008}. previous equivalent mtd period in may {01 may 2008 - 11 may 2008}.

i have next mdx script:

with fellow member [measures].[prevequivalentmtd] //sum ( parallelperiod ( [date].[calendar].[month] ,1 ,[date].[calendar].currentmember ) ,[measures].[internet sales amount] ) fellow member [measures].[prevequivalentmtd_v2] sum ( generate ( { [date].[calendar].[date].&[20080701] : [date].[calendar].[date].&[20080710] } ,{ parallelperiod ( [date].[calendar].[month] ,1 ,[date].[calendar].currentmember.item(0) ) } ) ,[measures].[internet sales amount] ) select { [date].[calendar].[month].&[2005]&[6] : [date].[calendar].[month].&[2008]&[7] } on rows ,{ [measures].[internet sales amount] ,[measures].[prevequivalentmtd] ,[measures].[prevequivalentmtd_v2] } on columns [adventure works];

it results in following:

the measure prevequivalentmtd not i'm looking returning total previous month rather first 10 days.

the measure prevequivalentmtd_v2 i'm unsure doing homecoming same number each month.

how create measure homecoming sum of net sales first 10 days of each month?

the periodstodate method come mind. not have 11th selected fellow member in query, using 11 given input.

with fellow member [measures].[prevequivalentmtd] sum(head([date].[calendar].currentmember.prevmember.children, 11), [measures].[internet sales amount]) select { [measures].[internet sales amount] ,[measures].[prevequivalentmtd] } on columns, { [date].[calendar].[month].&[2005]&[6] : [date].[calendar].[month].&[2008]&[7] } on rows [adventure works]

this taking first 11 (head( ,11)) children of month before (prevmember) current 1 row context of cell, , summing across these.

to 11 dynamically, e. g. utilize like

rank(tail([date].[calendar].[date]).item(0), tail([date].[calendar].[month]).item(0).item(0).children )

which determines position (rank) of lastly day of calendar hierarchy within parent month's children. assuming there no missing days e. g. weekends, should work. returns 30 adventure works, november, 30, 2010 lastly day in calendar hierarchy there.

ssas mdx olap