Get first date with sales and last date with sales in MDX

To get the first date with sales and last date with sales we can use the “tail” and the “head” function in MDX. These functions are quite handy if you want to have a default date in your reports.

Here is the code:

WITH MEMBER [Last Date With Sales] AS
  Tail (
    Filter ( [Date].[Calendar].[Date].Members, [Measures].[Internet Sales Amount]  NULL ),
    1
  ).Item ( 0 ) .MEMBER_CAPTION
MEMBER [First Date With Sales] AS
  Head (
    Filter ( [Date].[Calendar].[Date].Members, [Measures].[Internet Sales Amount]  NULL ),
    1
  ).Item ( 0 ) .MEMBER_CAPTION
SELECT
{ [Measures].[First Date With Sales], [Measures].[Last Date With Sales] } ON 0
FROM [Adventure Works]

And the result will look like this

Head and tail

Advertisements