KPI Help Required-I want a revenue report with average Daily Revenue

Hi All,

What I am trying to do is create a average daily revenue KPI using vs.net 2005.

The necessary fields from my fact table are Revenue and the datetimeID, there will be multiple entries perday.

I would like my KPI to check if we are hitting a $10,000 daily average in revenue. But I need to know how many Distinct Days are being calcuated. If I were using regular SQL I could use:

COUNT( SELECT DISTINCT dayNumberOfMonth,monthNumberOfYear,YearNumber)
or
COUNT(SELECT DISTINCT LEFT(datetimeAlternateKey,10))

My Current Datetime Table contains:
datetimeID
fulldatetime
monthNumberOfYear
calendarYear
dayNumberOfMonth

Any references out there on how I can go about doing this? More of less this is what I want to do:

Value Expression:
[Measures].[Revenue] * (Distinct Number Of days)

Goal Expression:
10,000*[Distinct Number of Days)

Any suggestions of books to look for or good web resources would be much appreciated.

Thanks all

What period would you be calculating this over and have you considered how you want partial periods treated?

Is the KPI meant to be "adaptive"? So if the user selects a month, it calculates the average for that month and if they select a year it calculates the average for the year?

Assuming that your Time dimension has a calendar hierarchy and a Day attribute, you could do something like the following:

Count(descendants([Time].[Calendar].CurrentMember, [Time].[Calendar].[Day]))

If there is $30,000 revenue recorded on day 1 of the month, does that mean that the KPI has been met for the whole month?

|||Thanks for the reply Darren,

When you refer to [Time].[Calendar].[Day], does [Day] represent an ongoing incrementing day count from the start of dates kept? i.e. Not day or month, nor day of year?

The KPI is meant to be adaptive, at a later point I will go for fixed KPIS (e.g. yesterday).

If for example our Goal Value was $1000 a day, and we did $30,000 in day. Our KPI would be met for any 30 day span that held that date. So right now my datetime table looks like this:

Do you have any suggestions on books I buy for Analysis Services / MDX?

Thanks
|||

winnipeg wrote:

When you refer to [Time].[Calendar].[Day], does [Day] represent an ongoing incrementing day count from the start of dates kept? i.e. Not day or month, nor day of year?

No, the first parameter to the descendants function picks up the current context of the time.calendar hierarchy. If the current context of time.calendar is a month member, the descendants will be all the days in the month. If it is a year member, it will be all the days in the year

winnipeg wrote:

Do you have any suggestions on books I buy for Analysis Services / MDX?

MDX Solutions: With Microsoft SQL Server Analysis Services 2005 and Hyperion Essbase by George Spofford, Sivakumar Harinath, Christopher Webb, and Dylan Hai Huang - is the best one I know for MDX

Applied Microsoft Analysis Services 2005: And Microsoft Business Intelligence Platform by Teo Lachev - is a good alround SSAS book

sql