First some quick background on what I am trying to do here. BTW, I am very
much an OLAP newbie.
I have been trying to figure out a way to compare current month sales to a
prorated target value. i.e. If our target for March was $1,000 and it was
March 15th, to track at 100% we should have sold $500 so far. The target is
a calculated member based on previous months sales.
What I did was create a new Fact table based on my time Dimension "FactTime"
and assign a value to each day based on how many days are in that month.
i.e. 1/31 or 1/30 (~.033).
This table is loaded only to the current date, I add a new record here each
night.
I then created a cube based on this table FactTime is the Fact and DimTime
is the only dimension. I then merged this Time cube into a virtual cube with
my Sales cube.

The measure "Daily Percent" from my Time cube works great when I am looking
at the Time Dimension. I see values of 1.0 for Jan, 1.0 for Feb, .55 For
March, 2.55 for Q1, 2.55 YTD. I then use this value to calculate how I am
tracking towards my targets. Here is my MDX:

IIF(
[Time].CurrentMember.Level.Ordinal = [Time].[Year].Ordinal,
[Measures].[Sales Amount] / (([Measures].[Sales Amount - Target
Amount])*([Measures].[Daily Percent]/12)),
IIF(
[Time].CurrentMember.Level.Ordinal = [Time].[Quarter].Ordinal,
[Measures].[Sales Amount] / (([Measures].[Sales Amount - Target
Amount])*([Measures].[Daily Percent]/3)),
IIF(
[Time].CurrentMember.Level.Ordinal = [Time].[Month].Ordinal,
[Measures].[Sales Amount] / (([Measures].[Sales Amount - Target
Amount])*([Measures].[Daily Percent]/1)), 1)))

For My other Dimensions (Product, Region, etc.), the "Daily Percent" value
is only populated at the "ALL" level. How can I get value populate to all
children of all my other dimensions?

Thanks for your help!

Also, if there is a better way to approach my original problem of tracking
towards targets mid-month, mid-quarter, mid-year, please let me know.

For the first question, you can use ValidMeasure():

http://groups-beta.google.com/group/...rver.olap/msg/
25ebd75ecc38d98e
From: Deepak Puri <deepak_p... (AT) progressive (DOT) com>Date: Wed, 02 Mar 2005
16:27:06 -0800
Local: Wed, Mar 2 2005 4:27 pm
Subject: Re: Bringing measures accross dimensions

Typically you would employ ValidMeasure() to do this:

http://msdn.microsoft.com/libr*ary/d...y/e*n-us/olapd
ma
d/agmdxfunctions_76cl.asp

ValidMeasure

Returns a valid measure in a virtual cube by forcing inapplicable
dimensions to their top level.

Syntax

ValidMeasure(«Tuple»)

Returns a valid measure from a tuple in a virtual cube specified in
«Tuple».

Remarks

When computing values in a virtual cube, measure cells only contain
values at the (All) level for dimensions that are not common between the
underlying cubes. The ValidMeasure function returns the measure value
from the cell at the (All) level coordinates for the dimensions that are
not common.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!