Count maximum sequel of null values - mdx query

I want to create a member based on this problem

I have a Product A being sold I want to find the largest range of consecutive days without sale

example:

days 1,2,3 the product not sale, after that,it sold for 15 consecutive days, at 19th day it didnt sell for 2 days and after that it sold every day until the end of the month

so my maximum days without sale was 3

Solution

The following query delivers in the Microsoft sample cube Adventure Works what you want:

WITH Member Measures.[days without sales] AS
            IIf( [Measures].[Internet Sales Amount] > 0
                , 0
                ,(Measures.[days without sales], [Date].[Calendar].CurrentMember.PrevMember) + 1
               )
     Member Measures.[Max days without sales] AS
            Max( [Date].[Calendar].[Date].Members
                ,Measures.[days without sales]
               )          
SELECT { [Measures].[Max days without sales] }
       ON COLUMNS
  FROM [Adventure Works]
 WHERE [Product].[Product].&[486]

The measure days without sales is defined recursively, and returns how many days up to and including the current member of the [Date].[Calendar] hierarchy there was no sales. You may need to adapt the criteria for "without sale", bearing in mind that in MDX, numerical comparisons treat NULL as 0 - which is different from SQL. This measure only works correctly if there is a member in this hierarchy for each day, i. e. there are no gaps in this hierarchy. And actually, the definition is more general than just working for days: If you use months for the [Date].[Calendar].CurrentMember, it would give you the number of months without sales, etc. It works with each level of the hierarchy.

The measure Max days without sales does not contain the product in its definition, it delivers the maximum days for whatever is in context (in this case the product in the WHERE clause).

Please note that - as actually there is a loop over all days in the [Date].[Calendar] hierarchy when calculating Measures.[Max days without sales], and within that the recursion again iterates along the previous days, and all this for each cell in the result set - this may be slow for large reports.