Search code examples
ssasmdx

MDX multiple filters

20 Yr SQL pro, new to MDX.

Trying to create a measure to get sales for products 30, 60, 90 days etc. after launch, but I want to exclude incomplete time periods. Here would be the sql:

select ProductName, sum(sales) '60DaySales' 
from dimProduct p join factSales s on p.productkey = s.productkey
  join dimCalendar c on s.orderDateKey = c.CalendarKey
   where datediff(dd,p.LaunchDate,c.Date) between 31 and 62
   and exists (select 1 from sales etc... where date >= 62 days)

Basically I only want to show '60DaySales' for products that also have sales beyond 62 days.

I have this MDX which gets me the time period:

sum(
  filter(
    [Sales].[Days Since Launch].members
   ,sales.[Days Since Launch].membervalue > 30 AND 
        sales.[Days Since Launch].membervalue < 63
  )
,[Measures].[SalesBase]
)

but I'm not sure how to exclude items with no sales beyond 62 days. I've tried some combinations of iif(exists.. ) and nonempty but no luck...

Solution

  • I'd add extra columns rather than a calculated measures. I had a similar tasks (Sales for 30,60,90 days, but from the first sale date of customer). The best way is to add a columns to your sale measure table:

    sales30 = iif(dateadd(day,30,p.LaunchDate) >= c.Date, sales, null),
sales60 = iif(dateadd(day,60,p.LaunchDate) >= c.Date, sales, null),
sales90 = iif(dateadd(day,90,p.LaunchDate) >= c.Date, sales, null)

    Tasks like sales 30 days per every product is doable via MDX, but they are performance killers for big dimensions. SQL Server does it better due to its concurrent nature. Meanwhile, MDX isn't good at heavy calculations like these. So I am not even providing the code.