I'll use AdventureWorks to frame my question, as then I can extend any suggestions to the possible applications I need.
I want to create a calculation which finds the change of each employee's monthly sales amount from their first month's sales.
e.g. for each month it will show how their sales for that month differs from the first month they ever made a sale.
What I'm struggling with is that one employee may have made their first sale in 2001, while another made their first sale in 20003. I'd like to be able to show how each employee's sales change relative to their first month, i.e. in month 2, month 3, etc.
I am thinking that I will need to define a new calculated measure which is the number of months elapsed for each employee since their first sale and then define a calculation for their deltas per month, but I'm not sure.
I've read up on OpeningPeriod() and the "Opening Period Balance" template, but I'm stuck. Using AS 2005.
Thanks for any suggestions.
-Leif Kirschenbaum
Leif,
Here is an example that I think will give you what you are looking for. I included a measure called "First Months Sales" which is just to show how the calculation is working and is not really needed for the end result.
HTH,
Steve
WITH
MEMBER [Date].[Calendar].[First Month With Sales]
AS
Filter([Date].[Calendar].[Month].Members, [Measures].[Reseller Sales Amount] > 0)(0)
MEMBER [Measures].[First Months Sales]
AS
([Date].[Calendar].[First Month With Sales],
[Measures].[Reseller Sales Amount]),
FORMAT_STRING = "currency"
MEMBER [Measures].[Difference]
AS
IIF(Exists({[Date].[Calendar].CurrentMember},{Filter([Date].[Calendar].[Month].Members, [Measures].[Reseller Sales Amount] > 0)(0).Lead(1):NULL}).Count = 1 AND
[Measures].[Reseller Sales Amount] > 0,
[Measures].[Reseller Sales Amount] - ([Date].[Calendar].[First Month With Sales],[Measures].[Reseller Sales Amount]),
NULL),
FORMAT_STRING = "currency"
SELECT
{[Date].[Calendar].[Month].Members} ON COLUMNS,
{[Reseller].[Reseller].[Bike Rims Company],
[Reseller].[Reseller].[Certified Sports Supply]} *
{[Measures].[Reseller Sales Amount],
[Measures].[First Months Sales],
[Measures].[Difference]} ON ROWS
FROM
[Adventure Works]
|||Great!
Thanks.
I do need one other thing, I need a dimension which counts the months from the first month for each employee. Right now in the browser when I drag Employee hierarchy to the rows field and Calendar to the columns field the difference for each employee starts in a different month. It would be useful to be able to drag "Months of Employment" to the columns field.
Would I do:
CREATE MEMBER CURRENTCUBE.[Date].[Calendar].[Months of Employment]
AS
[Date].[Calendar].[Month] - [Date].[Calendar].[First Month With Sales],
FORMAT_STRING = "Standard",
NON_EMPTY_BEHAVIOR = { [Reseller Sales-Sales Amount] },
VISIBLE = 1 ;
That doesn't work.
CREATE MEMBER CURRENTCUBE.[Measures].[Months of Employment]
AS
[Date].[Calendar].[Month] - [Date].[Calendar].[First Month With Sales],
FORMAT_STRING = "Standard",
NON_EMPTY_BEHAVIOR = { [Reseller Sales-Sales Amount] },
VISIBLE = 1 ;
also doesn't work, as I can't drag "Months of Employment" to the columns field.|||
Leif,
Try the following:
MEMBER [Measures].[Months of Employment]
AS
IIF(Exists({[Date].[Calendar].CurrentMember},{Filter([Date].[Calendar].[Month].Members, [Measures].[Reseller Sales Amount] > 0)(0).Lead(1):NULL}).Count = 1,
{Filter([Date].[Calendar].[Month].Members, [Measures].[Reseller Sales Amount] > 0)(0):[Date].[Calendar].CurrentMember}.Count - 1,
NULL),
FORMAT_STRING = "#,#"
