SSAS FAQ MDX FAQ MDX-Query to get count of months with sales amount > 0 in defined period
MDX-Query to get count of months with sales amount > 0 in defined period
User Rating: / 0
PoorBest 
Written by Vidas Matelis   
Thursday, 05 April 2007 18:06

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

< Prev   Next >