0845 643 64 63

Find first order date in MDX

I had an interesting question the other day; “how do I find the date of the first order/activity for a given customer/employee/product etc in MDX”?

Well you’ll be pleased to know that it’s pretty simple. The sample code bellow will work with the AdventureWorks DW 2008R2 cube.

First of all we need to filter the date dimension to contain only those with orders. Then take the first item of the resulting set, then find its name. Simples.

Filter the date hierarchy to only those dates with a Reseller Order Count:

   FILTER([Date].[Date].[Date].MEMBERS, [Measures].[Reseller Order count])

Note that you can specify a more advanced filter, such as [Measures].[Reseller Order Count]>10, which would find the first date which had more than 10 orders.

Then find the first item:

   .ITEM(0)

Then find the its name:

   .NAME

Put this together in a query and you get:

   WITH MEMBER [Measures].[First Activity] AS
       FILTER([Date].[Date].[Date].MEMBERS
            , [Measures].[Reseller Order count]).ITEM(0).NAME
   SELECT {[Measures].[Reseller Order count]
         , [Measures].[First Activity]
       } ON 0,
      [Employee].[Employees].MEMBERS ON 1
   FROM [Adventure Works]

This returns the total reseller order count for each employee, along with the date of their first reseller order. Note that the Employee hierarchy here is a parent child hierarchy. The calculated member [First Activity] will aggregate and calculate correctly with any dimension or member specified on the 1 (ROWS) axis, be it a parent-child, single attribute, normal hierarchy, etc. and will always find the first order date for the current member.
You should get the following results:

Frog-Blog Out.

27 Responses to Find first order date in MDX

  • Hoping you could help me with a calculations I am trying to define, it seems somewhat similar.

    Trying to define a calculation which will provide a last years $ sold for weeks select by the user but there are two calendars they can select from. The calculation gives $ sold for weeks select. The following works with one calendar.

    SUM(Existing [CalendarRetail].[Year-Period-Week].[Date].MEMBERS, (ParallelPeriod([CalendarRetail].[Year-Period-Week].[Year Number]), [Measures].[Dollars Sold]))

    Here are the results if I filter on last 4 most recent weeks. The values are the $ sold last year for the same week.

    Date Sales $ LY
    11/24/2012 $16,464,551
    12/01/2012 $18,666,707
    12/08/2012 $23,775,142
    12/15/2012 $28,025,152

    The calculations should return $ sold for each week, not a sum over the weeks. Is it possible to write the calculation to look at two calendars – CalendarRetail and CalendarFiscal (another calendar accessible by the user).

    Thanks
    Bill

    • Hi Bill

      There are a couple of approaches to this, depending on whether it’s in a query or can be done in the MDX calculations script.

      A simple solution is to check ParallelPeriod on both date hierarchies and then decide which one to use by checking the currentmember of one of the date hierarchies. i.e. If the currentmember of one hierarchy is ALL then use the other hierarchy. This is a technically poor solution in terms of performance, we should avoid IIF statements where possible, but it gives us a starting point.

      This code does what you want on the Adventure Works DW 2008R2 cube.

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      
      WITH MEMBER [Measures].[Sales LY] AS
          IIF([DATE].[Fiscal Weeks].CurrentMember IS [DATE].[Fiscal Weeks].[ALL]
              , (PARALLELPERIOD([DATE].[Calendar Weeks].[Calendar YEAR],1), [Measures].[Internet Sales Amount])
              , (PARALLELPERIOD([DATE].[Fiscal Weeks].[Fiscal YEAR],1), [Measures].[Internet Sales Amount]))
      SELECT
          {[Measures].[Internet Sales Amount]
           ,[Measures].[Sales LY]
          } ON 0,
          DESCENDANTS([DATE].[Calendar Weeks]) ON 1
          --DESCENDANTS([Date].[Fiscal Weeks]) ON 1
      FROM [Adventure Works]

      For a better solution, the switching between different date hierarchies should be done using SCOPE in the MDX calculation script instead of IIF. Have a look at https://www.purplefrogsystems.com/blog/2009/11/scope-problems-with-mdx-calculated-members/ to give you a pointer on how to do this.

      Alex

      • Alex

        Thanks. Implement the measure using Scope and it works perfectly!

        CREATE MEMBER CURRENTCUBE.[Measures].[ Sales $ LY]
        AS “”,FORMAT_STRING = “$#,##;($#,##)”, VISIBLE = 1;
        Scope([CalendarRetail].[Year-Period-Week].[Date].MEMBERS);
        [Measures].[ Sales $ LY]=Sum(Existing [CalendarRetail].[Year-Period-Week].[Date].MEMBERS, (ParallelPeriod([CalendarRetail].[Year-Period-Week].[Year Number]), [Measures].[Dollars Sold]));
        End Scope;
        Scope([CalendarFiscal].[Year-Period-Week].[Date].MEMBERS);
        [Measures].[ Sales $ LY]=Sum(Existing [CalendarFiscal].[Year-Period-Week].[Date].MEMBERS, (ParallelPeriod([CalendarFiscal].[Year-Period-Week].[Year Number]), [Measures].[Dollars Sold]));
        End Scope;

        Thanks
        Bill

  • If i want to get last order date then how it will be possible

    • Using ITEM(0) gets you the first member of a set. You can get the last member by using TAIL().

      Technically TAIL() returns a set, so you then need to return the first member of that set, using ITEM(0) as before.

      TAIL(FILTER([Date].[Date].[Date].MEMBERS, [Measures].[Reseller Order count])).ITEM(0).NAME

      Hope this helps
      Alex

      • Hi

        Tail() command helped very much in my previous project. Thank you Alex.

        My new project is also to find the latest Startdate of an employee but I have to display the corresponding enddate (or NULL if the employee is still working). along with department name linked to the latest date.

        WITH

        MEMBER [Measures].[LatestStartDate] AS
        sum(
        tail(
        nonempty([Date].[Full Date].CurrentMember,[Measures].[No_of_Emp])
        ,1)
        ,[Measures].[No_of_Clients]
        )

        MEMBER [Measures].[LatestEmpStartDATE] AS
        TAIL(FILTER([Emp_StartDate].[Full Date].MEMBERS
        , [Measures].[LatestStartDate])).ITEM(0).NAME

        MEMBER [Measures].[LatestEmpEndDATE] AS
        TAIL(FILTER([Emp_EndDate].[Full Date].MEMBERS
        , [Measures].[LatestStartDate])).ITEM(0).NAME

        SELECT
        {
        MEASURES.[LatestStartDate]
        , [Measures].[LatestEmpStartDATE]
        , [Measures].[LatestEmpEndDATE]
        } on columns

        ,NON EMPTY
        [Employee].[EmpID].members
        on ROWS

        Form this code I am getting LatestEmpEndDATE but not linked to the corresponding LatestEmpStartDATE.

        Thanks in advance

        • There’s nothing in your code that restricts the end date to only that corresponding to the equivalent start date.
          So to fix it you need to restrict the set of end dates to only those after the latest start date.
          You can do that using the LinkMember function; find the last start date, then map that date to the end date dimension, then only use dates after that to find the last end date.

          Something like this

          MEMBER [Measures].[LatestEmpStartDate] AS
          TAIL(NONEMPTY([Emp_StartDate].[Full Date].[Full Date].MEMBERS, [Measures].[No_of_Emp]),1).ITEM(0).MEMBER_NAME

          MEMBER [Measures].[LatestEmpEndDate] AS
          TAIL(NONEMPTY(
          {LINKMEMBER(TAIL(NONEMPTY([Emp_StartDate].[Full Date].[Full Date].MEMBERS, [Measures].[No_of_Emp]),1).ITEM(0)
          ,[Emp_EndDate].[Full Date]) : null}
          , [Measures].[No_of_Emp]),1).ITEM(0).MEMBER_NAME

          • Hi Alex
            Thank you very much. This code worked for me..
            Thanks again

          • Dear Alex

            If I use the same technique to bring in the Manager ID of the latest Start Date, I am getting Null values. Please advise, whehter I am missing some thing here

            Thank you once again in advance

            WITH
            MEMBER [Measures].[LatestEmpStartDate] AS
            TAIL(NONEMPTY([Emp_StartDate].[Full Date].[Full Date].MEMBERS, [Measures].[No_of_Emp]),1).ITEM(0).MEMBER_NAME

            MEMBER [Measures].[LatestEmpEndDate] AS
            TAIL(NONEMPTY(
            {LINKMEMBER(TAIL(NONEMPTY([Emp_StartDate].[Full Date].[Full Date].MEMBERS, [Measures].[No_of_Emp]),1).ITEM(0)
            ,[Emp_EndDate].[Full Date]) : null}
            , [Measures].[No_of_Emp]),1).ITEM(0).MEMBER_NAME

            MEMBER [Measures].[LatestMGR] AS
            TAIL(NONEMPTY(
            {LINKMEMBER(TAIL(NONEMPTY([Emp_StartDate].[Full Date].[Full Date].MEMBERS, [Measures].[No_of_Emp]),1).ITEM(0)
            , [DIM_Manager].[Mgr_Code])}
            , [Measures].[No_of_Emp]),1).ITEM(0).MEMBER_NAME

            SELECT
            {
            [Measures].[LatestEmpStartDate]
            , [Measures].[LatestEmpEndDate]
            , [Measures].[LatestMGR]
            } on columns

            ,NON EMPTY
            [Employee].[EmpID].members
            on ROWS
            From Cub_

          • Dear Alex

            If I use the same technique to bring in the Manager ID of the latest Start Date, I am getting Null values. Please advise, whehter I am missing some thing here

            Thank you once again in advance

            WITH
            MEMBER [Measures].[LatestEmpStartDate] AS
            TAIL(NONEMPTY([Emp_StartDate].[Full Date].[Full Date].MEMBERS, [Measures].[No_of_Emp]),1).ITEM(0).MEMBER_NAME

            MEMBER [Measures].[LatestEmpEndDate] AS
            TAIL(NONEMPTY(
            {LINKMEMBER(TAIL(NONEMPTY([Emp_StartDate].[Full Date].[Full Date].MEMBERS, [Measures].[No_of_Emp]),1).ITEM(0)
            ,[Emp_EndDate].[Full Date]) : null}
            , [Measures].[No_of_Emp]),1).ITEM(0).MEMBER_NAME

            MEMBER [Measures].[LatestMGR] AS
            TAIL(NONEMPTY(
            {LINKMEMBER(TAIL(NONEMPTY([Emp_StartDate].[Full Date].[Full Date].MEMBERS, [Measures].[No_of_Emp]),1).ITEM(0)
            , [DIM_Manager].[Mgr_Code])}
            , [Measures].[No_of_Emp]),1).ITEM(0).MEMBER_NAME

            SELECT
            {
            [Measures].[LatestEmpStartDate]
            , [Measures].[LatestEmpEndDate]
            , [Measures].[LatestMGR]
            } on columns

            ,NON EMPTY
            [Employee].[EmpID].members
            on ROWS
            From Cub_

  • This worked for me. The first few results I checked suggested using the TOPCOUNT function for this situation, but I couldn’t get that working. Thanks.

  • Thanks for the article. Can you please expand the query to total the number of First Activities per Month / Day. This can also represent the number of new users / activities?

    Thanks a lot!
    J

    • Hi John
      Yes sure – just add in another calculated measure…

      MEMBER [Measures].[Activities In First Month] AS
      ([Measures].[Reseller Order count]
      , FILTER([Date].[Date].[Date].MEMBERS
      , [Measures].[Reseller Order count]).ITEM(0)
      )

      This calculates the order count measure, but filtered to only the first member of the date hierarchy that contains a value.

      Alex

  • Hi Alex,

    Thanks for this article. It helps a lot!
    Following your example, I was able to get the first payment date and the first sale date. However, it threw error “Type Mismatch” when i tried to subtract those 2 dates, FirstPmtDate – FirstSaleDate. Any suggestion?

    Thanks,
    Jane

    • Hi Jane
      Sorry for delay in reply. You can’t just subtract dates in MDX, but you can use Datediff to achieve this.
      Chris has a good example here: http://cwebbbi.wordpress.com/2014/06/09/calculating-ages-in-mdx/
      Regards
      Alex

  • I have a scenario where a product is introduced on ‘1/1/2014’ and another product on ’10/1/2014” what to find out the first 30 day sales of both and compare

    • This is a data modelling problem, not an MDX problem. You need to add a ‘Product Age’ dimension, representing the number of days between the launch of a product and the date of each sale.

      • in general how to calculate first 30 days sum,60 days sum,90 days sum,120 days sum in cubes.

        • To sum up a range you can just use SUM({set}, measure)
          So SUM({[Date].[Date].Lag(29):[Date].[Date].currentmember}, [Measures].[Sales])
          Would sum up over the last 30 days.

          The problem is that you don’t want the last 30 days, you want the first 30 days from the date a product launched. And that date changes from product to product. So how do you define the first day from which to start your set from. And that’s why you should look to do this with an extra dimension and not using complex MDX.

          • Got it. I have week of year total. Can i further drill down so that i can do (week of year total)/Number of Days in that week ?

          • You could have a new measure group, with the granularity of date, and a value of 1 in each row. This is the fastest option, but relies on adding a new measure group to the cube.
            Alternatively you could use something like:
            COUNT(Descendants([Date].[Calendar].CurrentMember,,LEAVES),INCLUDEEMPTY)
            Which will could the number of leaf members of the Calendar hierarchy in the Date dimension.

  • in general how to calculate first 30 days sum,60 days sum,90 days sum,120 days sum in cubes

  • I’m new to cubes currently filling the gap of my teammate who is on vacation.. which books should i go through to get a fair understanding of SSAS, mdx queries

    • My favourite MDX book has always been “Fast Track to MDX” by Whitehorn, Zare & Pasumansky.
      It’s out of print now so can be expensive and difficult to get hold of.
      Alternatively you can’t go far wrong with anything by Chris Webb, Alberto Ferrari or Marco Russo

  • Hi Alex,

    I need some help. I am trying calculated the Exponential Moving Average, and i need get the first 9 dates in the AVG, and after 9 using the formula. I do everything using the formula to EMA, but i cant get the first 9 dates.

    ill be appreciate for your help.

    • Hi Tulio
      To get the first 9 dates you can just use the MDX Head function:
      Head({Set}, 9)
      In this case, the set would be the members of the date dimension, so:
      Head({[Date].[Date].[Date].Members}, 9)
      Or you may need to add a nonempty around it, to make sure you don’t include blank dates
      Head(NONEMPTY([Date].[Date].[Date].Members, [Measures].[xxx]), 9)

Leave a Reply

Your email address will not be published. Required fields are marked *

HTML tags are not allowed.

346,677 Spambots Blocked by Simple Comments

The Frog Blog

I'm Alex Whittles.

I specialise in designing and implementing SQL Server business intelligence solutions, and this is my blog! Just a collection of thoughts, techniques and ramblings on SQL Server, Cubes, Data Warehouses, MDX, DAX and whatever else comes to mind.

Data Platform MVP

Frog Blog Out
twitter
rssicon