Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
MDX - Next 90 days and Las 90 Days of Data

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

  • Wednesday, February 13, 2013 10:09 PM
     
     
    Sign In to Vote
    0

    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])

    Can you please help me with this. Thanks

    Tinku

     

All Replies

  • Wednesday, February 13, 2013 10:38 PM
     
     
    Sign In to Vote
    0

    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
     
     Answered
    Sign In to Vote
    0

    Hi,

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

    Regards,

    Zaim Raza

     
  • Thursday, February 14, 2013 3:25 PM
     
     
    Sign In to Vote
    0
    Thanks Zaim. It worked.