ssasmdxsharepoint-2013dimensionperformancepoint

Non-empty previous value - MDX

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

Solution

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