MDX - Next 90 days and Las 90 Days of Data

# MDX - Next 90 days and Las 90 Days of Data

• Wednesday, February 13, 2013 10:09 PM

Hi All,

I have to create two different calculated measures on a measure, one for last 90 days based on Startdate and other for next 90 days based on enddate.

Two time dimensions(StartDate, Enddate)

One measure group - a measure.

I created two calculated measures one for each and the cube is working fine for each of them individually but returns blank values when I use both date filters.

[Last 90days] =  SUM({[Start Date].[Date].CurrentMember.Lag(90):[Start Date].[Date].CurrentMember},[Measures].[Amount])

[Next 90days] = SUM({[End Date].[Date].CurrentMember:[End Date].[Date].CurrentMember.Lead(90)},[Measures].[Amount])

Tinku

### All Replies

• Wednesday, February 13, 2013 10:38 PM

Found the solution.

[Last 90days] =  SUM({[Start Date].[Date].CurrentMember.Lag(90):[Start Date].[Date].CurrentMember} * [End Date].[Date],[Measures].[Amount])

[Next 90days] = SUM({[End Date].[Date].CurrentMember:[End Date].[Date].CurrentMember.Lead(90)} * [Start Date].[Date] ,[Measures].[Amount])

It is very slow. Let me know if there is any other better way.

• Wednesday, February 13, 2013 11:37 PM

Hi,

Use NONEMPTYCROSSJOIN() function becuase your sets my contain empty data as well as.

Regards,

Zaim Raza

• Thursday, February 14, 2013 3:25 PM

Thanks Zaim. It worked.