Non-empty previous value - MDX

2
votes

I am using Performance Point Dashboard Designer 2013 and SharePoint Server 2013 for building dashboards. I am using SSAS2012 for Cube.

I have a scenario similar to the one illustrated by figure below. I am required to find Previous Non-Empty value for purpose of finding Trends.

Previous Non-empty

Measure: [Quota]

Dimension: [Date].[Calendar Date].[Date]

The script ([Measures].[Quota], [Date].[Calendar Date].PrevMember) gives you a previous date. Lets say for date 27-Jan-13 whose Quota value is 87, it returns 26-Jan-13 which has null value. I want it to return 21-Jan-13 that has some Quota value. And for date 21-Jan-13, I want to return 15-Jan-13.

I wonder if this is possible.

Thanks,
Merin

2
ssasmdxsharepoint-2013dimensionperformancepoint

2 Answers

4
votes

After long searches and hits & trials and so on, I think I invented a solution of my own for myself.

Following is the script for my Calculated Member.

(
 [Quota],
 Tail
  (
   Nonempty
   ( LastPeriods(15, [Date].[Calendar Date].PrevMember)
    ,[Quota]
   )
  ).Item(0)
)

Explanation

  1. The number 15 means it will look for non-empty measures up to 15 siblings.
  2. Now we know up to how many siblings to traverse back, in this case 15.

  3. Lets find 15 previous siblings (both empty and non-empty) excluding current member.

    (LastPeriods(15, [Date].[Calendar Date].PrevMember)

  4. Since it will yield both empty and non-empty members, lets filter out empty members in terms of measure [Quota]. If we don't specify measure here, it will use default measure whatever it is and we may not get desired result.

    Nonempty(LastPeriods(15, [Date].[Calendar Date].PrevMember),[Quota])

  5. We may have several members in the output. And we will choose the last one.

    Tail ( Nonempty ( LastPeriods(15, [Date].[Calendar Date].PrevMember) ,[Quota] ) )

  6. So far, the script above gives previous non-empty member. Now we want to implement this member for our measure [Quota].

Hence we get the script below ready to create a Calculated Member.

(
 [Quota],
 Tail
  (
   Nonempty
   ( LastPeriods(15, [Date].[Calendar Date].PrevMember)
    ,[Quota]
   )
  ).Item(0)
)
1
votes

You can use recursion to define this.

The following query delivers something similar for the Adventure Works cube:

WITH member [Measures].[Prev non empty] AS
            IIf(IsEmpty(([Date].[Calendar].CurrentMember.PrevMember, [Measures].[Internet Sales Amount])),
                ([Date].[Calendar].CurrentMember.PrevMember, [Measures].[Prev non empty]),
                ([Date].[Calendar].CurrentMember.PrevMember, [Measures].[Internet Sales Amount])
               ), format_String = '$#,##0.00'

SELECT {[Measures].[Internet Sales Amount], [Measures].[Prev non empty]}
       ON COLUMNS,
       non empty
       Descendants([Date].[Calendar].[Month].&[2007]&[12], [Date].[Calendar].[Date])
       ON ROWS
FROM [Adventure Works]
WHERE [Customer].[Customer].&[12650]

You would have to replace the name of the date hierarchy, as well as the measure name from Internet Sales Amount to Quota in the recursive definition of the measure Prev non empty.