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

• ### Question

• 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

Wednesday, February 13, 2013 10:09 PM

• Hi,

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

Regards,

Zaim Raza

Wednesday, February 13, 2013 11:37 PM

### All replies

• 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 10:38 PM
• Hi,

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

Regards,

Zaim Raza

Wednesday, February 13, 2013 11:37 PM
• Thanks Zaim. It worked.
Thursday, February 14, 2013 3:25 PM