none
MDX - Next 90 days and Las 90 Days of Data RRS feed

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

    Can you please help me with this. Thanks

    Tinku

    Wednesday, February 13, 2013 10:09 PM

Answers

  • 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