Calculate Average Age on Entry

I have a fact table that tracks a person enrollment in a program.  In short the fact table is…

PersonKey

ProgramKey

TimeKey

Age (in days)

IsEntry

Each night during the ETL I add a row for each client that is enrolled.  I need a calculation that will give me the average age at enrollment (IsEntry = 1) of all people in a program by time.  I am new to MDX and don’t know how to add the equivalent of a where clause to the expression.  If it helps here is the T-SQL I would use.

SELECT AVG(Age)/364.25

FROM dbo.FactEnrollment

WHERE FacilityKey = 5

       AND IsEntry = 1

       AND TimeKey BETWEEN 20080701 AND 20090630

 Thanks for the help.

Here is an example of a Where clause I wrote against the Adventure Works R2 cube:

where ([Reseller].[Business Type].&[Warehouse],
	[Source Currency].[Source Currency].&[US Dollar],
	[Date].[Calendar Year].&[2006]:[Date].[Calendar Year].&[2008])

This query limited my results to a Warehouse business type, US Dollar currency, and years between 2006 and 2008. This is just an example, so play around with it.

Thanks for the post.  Can I put something like that in as a calculation in the cube?

Here is my average age calculation.

([Measures].[Age] / [Measures].[Fact Enrollment Count])/364.25

How would I change it so that it is only looking at records with IsEntry  = 1?

Still don’t have an answer to this question so I am going to try to ask it in a different way.  In my cube how can I filter the data in the fact table?  Do I need to make a dimension with the values that I want to filter on and use that?  In this example I would like to filter the fact table to only show records where IsEntry is equal to 1.  I have other problems like this I need to solve, and if my method is incorrect I would really like to know that before I proceed and make a mess.

Thanks

If that is all you need on this cube,  I would suggest the dimension's  data source view having the sql similar to the one you indicated in your first post.

That means that your dimensions will only have the rows specific to IsEntry = 1.

thanks,
Luis

I added a Boolean dimension that I now can use to filter the IsEntry and IsExit fields.  With this the calculation I have for average age can be used for either entry or exit records.  I don’t know if this is the best way to do this, but it does work.