MDX-Query to get count of months with sales amount > 0 in defined period

Q: MDX query to get count of months with sales amount > 0 in defined period

A: To get number of months with sales >0 you can use COUNT() function with FILTER() function inside. Example below shows Sales amount by product line in year 2003, 2004 and also shows number months in that period that specific product line had sales.

WITH Member [Measures].[Months With Above Zero Sales] AS
COUNT(FILTER(
DESCENDANTS({[Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004]}
, [Date].[Calendar].[Month])
, [Measures].[Sales Amount] > 0
)
)
SELECT {[Measures].[Sales Amount], [Measures].[Months With Above Zero Sales]} ON 0
, [Product].[Product Model Lines].[Product Line].Members on 1
FROM [Adventure Works]
WHERE ([Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004])

Results will be:

Sales Amount Months With Above Zero Sales
Accessory $1,987,396.37 19
Components $454,644.34 18
Mountain $24,430,307.51 19
Road $24,919,506.74 19
Touring $16,010,837.10 13