calculated measure -- aggregating sales data by set time constraints

[rgaertner]

I'm trying to create a calculated measure that will aggregate sales data for each Monday in the 1st week of the month. I was able to calculate the total using the SQL formula below, but was wondering if anyone had the MDX equivalent?

set @mon1sales = (select sum(sales) as totalsales from salestable1, Dim_Calendar_Date2 where salestable1.start_date = Dim_Calendar_Date2.FullSQLDate and datepart(dw, salestable1.start_date) = 2 and Dim_Calendar_Date2.PositioninMonth = 1)

Thanks.

 

[CodA75]

Hello, you may add 2 properties to the levels of your time dimension : "start_date" and "PositioninMonth" and then use the FILTER MDX function to retrieve only the data you want :

something like :
{Filter( [Measures].[SALES_DATA], [TIME].currentmember.propety("start_date&quot = 2 AND [TIME].currentmember.propety("PositioninMonth&quot = 1)}

Please check the syntax as I'm writing it directly there ...
A+