# MDX - Aggregate a measure based on a specific start time

• ### Question

• Hi,

I would like to aggregate a measure from a specific start date to the end of my time scope. How do I solve this?

Friday, June 8, 2007 9:50 AM

### All replies

• Take a look at this sample.  There may be more going on here than you need so you may want to simplify the logic.

Let's start with the axes, I want Reseller Sales and 2003 Holiday Sales on my columns.  If I were doing this in the real world, I would not use specific dates but instead identify year-independent day ranges for this kind of thing, but for this example, I'm looking at just 2003.

The row axis generates a list of all members in calendar year 2003. You'll see year, quarters, months, and days in the axis. I do this so you can see the rollup.

The calculated member says build a set of dates from Nov 1 2003 to Dec 31 2003.  Use the Exists function to limit this to the current Date member.  (You can remove the exists function and the [Date].[Calendar].CurrentMember to see why I did this.) Then, for the members returned in that set definition, grab the Reseller Sales measure.  Aggregate that measure and return.

Code Snippet

with member [Measures].[2003 Holiday Season Sales] as

AGGREGATE(

EXISTS(

{[Date].[Calendar].[Date].[November 1, 2003]:[Date].[Calendar].[Date].[December 31, 2003]},

[Date].[Calendar].CurrentMember

),

[Measures].[Reseller Sales Amount]

)

select

{

[Measures].[Reseller Sales Amount],

[Measures].[2003 Holiday Season Sales]

} on 0,

EXISTS([Date].[Calendar].Members,[Date].[Calendar].[Calendar Year].[CY 2003]) on 1

Friday, June 8, 2007 1:27 PM
• In your example you have set your date range from November 2003 to December 2003, but I don´t know the end date and that´s my problem. It should aggregate as long as there´s data for my measure. I agree with you that I should use the aggregate function together with the exists function but I would like to set the end date somehow or make a more dymanic solution, for example I look in what date member who doesn´t have any data and then I found this one I take the previuos one. I feel that my explanation isn´t good enough but I hope that you understand what I´m suppose to do.

The analyze tool I´m using is ProClarity, if you familiar with that.

Regards,

Inmon

Friday, June 8, 2007 7:15 PM
• It's hard to say exactly what the calculated member(s) might look like, but I'll try to offer some bits and pieces that might help get you where you want to go.

First, you can identify the last date with which a fact is associated using a combination of EXISTS and TAIL.  This will give you your period end date. Here's a simple example:

Code Snippet

select

{} on 0,

TAIL(

HIERARCHIZE(

EXISTS(

[Date].[Date].[Date].Members,,'Reseller Sales'

)

),

1)  on 1

Knowing that value, you can use PARALLELPERIOD() or the VBA date functions to go some number of dates back to get the period start date.

Knowing the date start and end, you can then use AGGREGATE to sum the values of your measure across that range.

Hope that helps,

Bryan

Monday, June 11, 2007 1:01 PM