In TSQL, we could do it using a recursive CTE that iterates backwards by one date at a time.
WITH BaseQuery(FullDateAlternateKey, OrderCount) AS
(
SELECT d.FullDateAlternateKey, COUNT(dt.OrderDateKey) AS OrderCount
FROM dbo.DimDate d
LEFT JOIN
(SELECT s.OrderDateKey, p.Color
FROM dbo.FactInternetSales s
INNER JOIN dbo.DimProduct p ON s.ProductKey = p.ProductKey) dt
ON d.DateKey = dt.OrderDateKey AND dt.Color = 'Yellow'
WHERE d.CalendarYear = 2003
GROUP BY d.FullDateAlternateKey
), DaysSinceLast_Recursive(FullDateAlternateKey, OrderCount, DaysSinceLastOrder) AS
(
-- anchor members
SELECT FullDateAlternateKey, OrderCount, 0 AS DaysSinceLastOrder
FROM BaseQuery b
WHERE OrderCount > 0
UNION ALL
-- recursive members
SELECT b.FullDateAlternateKey, b.OrderCount,
d.DaysSinceLastOrder + 1 AS DaysSinceLastOrder
FROM BaseQuery b
INNER JOIN DaysSinceLast_Recursive d ON
DATEADD(day, -1, b.FullDateAlternateKey) = d.FullDateAlternateKey
AND NOT b.OrderCount > 0
)
SELECT * FROM DaysSinceLast_Recursive
ORDER BY FullDateAlternateKey
Or we could just get the max date with at least one order prior to the current date - and then use DATEDIFF to count the number of days.
WITH BaseQuery(FullDateAlternateKey, OrderCount) AS
(
SELECT d.FullDateAlternateKey, COUNT(dt.OrderDateKey) AS OrderCount
FROM dbo.DimDate d
LEFT JOIN
(SELECT s.OrderDateKey, p.Color
FROM dbo.FactInternetSales s
INNER JOIN dbo.DimProduct p ON s.ProductKey = p.ProductKey) dt
ON d.DateKey = dt.OrderDateKey AND dt.Color = 'Yellow'
WHERE d.CalendarYear = 2003
GROUP BY d.FullDateAlternateKey
)
SELECT FullDateAlternateKey, OrderCount,
CASE WHEN OrderCount > 0 THEN 0
ELSE
DATEDIFF(day,
(SELECT MAX(FullDateAlternateKey) FROM BaseQuery b2
WHERE b2.FullDateAlternateKey < b.FullDateAlternateKey AND
b2.OrderCount > 0),
FullDateAlternateKey)
END DaysSinceLastOrder
FROM BaseQuery b
ORDER BY FullDateAlternateKey
What about MDX? We might be tempted to do it recursively in MDX.
(This code is just illustrating the concept – it doesn’t check the current level of the Calendar hierarchy, aggregate up the Calendar hierarchy, etc.)
WITH MEMBER [Measures].[Days Since Last Order] AS
//check if current date member has order count that is empty or 0
IIf( ([Date].[Calendar].CurrentMember,
[Measures].[Internet Order Count]) = 0,
([Date].[Calendar].CurrentMember.PrevMember,
[Measures].[Days Since Last Order]) + 1,
Null
)
SELECT
{[Measures].[Internet Order Count],
[Measures].[Days Since Last Order]} ON 0,
[Date].[Calendar].[Date].Members ON 1
FROM [Adventure Works]
WHERE ([Product].[Color].&[Yellow],
[Date].[Calendar Year].&[2003])
which returns what we want …
No faffing around with dates. We leverage the inherent ordering of members in Analysis Services using .PrevMember.
But you may be aware that recursive MDX is often prone to performance problems. It works OK on AdventureWorks, but take my word for it – it would run like a 3-legged dog on large volumes.
The following MDX version would perform much faster ...
WITH MEMBER [Measures].[Days Since Last Order] AS
Count( Tail( NonEmpty( {Null : [Date].[Calendar].CurrentMember},
[Measures].[Internet Order Count] ),
1).Item(0) : [Date].[Calendar].CurrentMember
) - 1
SELECT
{[Measures].[Internet Order Count],
[Measures].[Days Since Last Order]} ON 0,
[Date].[Calendar].[Date].Members ON 1
FROM [Adventure Works]
WHERE ([Product].[Color].&[Yellow],
[Date].[Calendar Year].&[2003])
and returns the same results (almost)
The above MDX again depends on the inherent ordering of members in Analysis Services, but this time it gets the last non null date (using the Tail function) and then counts the number of members between that and the Calendar current member.
How about DAX? A DAX measure can't call another measure (which seems like a major limitation - maybe that will change as they enhance the language), let alone call itself recursively.
But the following does work. Note: I had to convert the OrderDateKey in FactInternetSales to type datetime to get it to work.
=CALCULATE( COUNTROWS( DimDate ) - 1,
DATESBETWEEN( DimDate[FullDateAlternateKey],
LASTNONBLANK( DATESBETWEEN( DimDate[FullDateAlternateKey],
BLANK(),
LASTDATE( DimDate[FullDateAlternateKey] )
),
CALCULATE( COUNTROWS( FactInternetSales ) )
),
LASTDATE( DimDate[FullDateAlternateKey] )
)
)
I think the DAX version is somewhere in between the 2nd TSQL version and the 2nd MDX version.