Subscribe to this blog
Briefcase
Print
This post is a part of a series of blog posts I am writing to give you a Batman-like Utility Belt of MDX calculations. With this Utility Belt available you can have a template of commonly needed calculations you can use for just about any Analysis Services project.
In part one of this series I showed how you could use the function ParallelPeriod to return sales from the previous year at all levels (Year, Quarter, Month).
In this series I present the business problem, a general solution, and the calculation to solve that problem.
Problem
To show the sales for the previous date member. (Ex. Show the sales for the last year, last quarter, or last month)
Solution
Using a function called PrevMember you can return the value of previous member.
Calculations
The sales for the previous year
([Date].[Year].PrevMember,[Measures].[Sales Amount])
---------------------------------------------------------------
The sales for the previous quarter
([Date].[Quarter].PrevMember,[Measures].[Sales Amount])
---------------------------------------------------------------
The sales for the previous month
([Date].[Month].PrevMember,[Measures].[Sales Amount])
Posted by Paul Mak-346751 on 7 December 2009
Is there a MDX function if I want to find out on a given date, how many unique customer for the past 2 years? Also how many are new or repeat customers for the past 2 years?
Thanks.
Posted by knight_devin@hotmail.com on 11 December 2009
Yeah it sounds like you may want to use the lag function. SELECT
[Measures].[Internet Sales Amount] on 0,
[Ship Date].[Calendar Year].[All Periods].LastChild.Lag(1)
on 1
FROM [Adventure Works]
I would need to see how the cube is structured for the second question.