30 July 2016

All Member

All Member is a very handy member to use in some MDX calculations. There is a need to remember, when you drop any measure and no other dimension members, it doesn't mean that they aren't in use. Every cell (tuple) has a coordinate. If you don't select any member from the hierarchy it doesn't mean that your current coordinate is unknown, it's .DefaultMember, most likely it is .All member (you can change default member in the Attribute properties).

New clients case

In order to count clients who have first order in current period we may use min date of invoice in current period, comparing it with the overall min date history. If they match, then it's a new client.

SUM(
    [Client].[Client].[Client].Members,
    IIF(
        [Measures].[Invoice First Date] > 0 --check the first invoice this period
        and
        [Measures].[Invoice First Date] = ([Date].[Day].[All],[Date].[Month].[All],[Date].[Year].[All],[Measures].[Invoice First Date]), --check the first invoice all time and compare with current value
        1,
        NULL
    )
)



As you may have already mentioned the first month obviously has the biggest value, that's because the starting point for recording invoices is Oct 2012. Now we have a great toolkit to analyze the efficiency of the marketing department promotions by comparing previous periods with promotion periods aftermath.

   MDX, SSAS

© Danylo Korostil