Finding related date to Calculated Measure in MDX

Question

BackStory I am working in MDX and my measure groups use many calculations. Many of these are timeframes such as [Date].[Fiscal].[LY WTD] which stands for last year week to date.

To calculate this I was using the ParellelPeriods function. However as our calendar has 52 weeks for one year and 53 for the current year my calculations are off.

I have made my changes using

CREATE MEMBER CURRENTCUBE.[Date].[Fiscal].[LY WTD] as
Aggregate(
  Exists(
    [Date].[Date].[Date].members,
      {
        [Date].[F Day Of Week].[F Day Of Week].item(0) :
        Exists([Date].[F Day Of Week].[F Day Of Week].members, [Date].[Last Complete Day].&[Y]).item(0)
      }
    * Exists([Date].[F Year].[F Year].members,[Date].[Last Complete Day].&[Y]).item(0).lag(1)
    * Exists([Date].[F Week of Year].[F Week of Year].members, [Date].[Current Week].&[Current Week])
  )
), VISIBLE = 0;

I now need to test this.

Problem How can I determine what week my measure [Date].[Fiscal].[LY WTD] is pulling back?

Answer 1

Could you add a test member like this?

CREATE 
  MEMBER CURRENTCUBE.[Date].[Fiscal].[LY WTD - Caption] AS 
    Exists
    (
      [Date].[F Week of Year].[F Week of Year].MEMBERS
     ,
        {
            [Date].[F Day Of Week].[F Day Of Week].Item(0)
          : 
            Exists
            (
              [Date].[F Day Of Week].[F Day Of Week].MEMBERS
             ,[Date].[Last Complete Day].&[Y]
            ).Item(0)
        }*
        Exists
        (
          [Date].[F Year].[F Year].MEMBERS
         ,[Date].[Last Complete Day].&[Y]
        ).Item(0).Lag(1)*
        Exists
        (
          [Date].[F Week of Year].[F Week of Year].MEMBERS
         ,[Date].[Current Week].&[Current Week]
        )
    ).Item(0).Item(0).Member_Caption 
   ,VISIBLE = 1 ;

Answer 2

I think my problem is that as I am aggregating my calc script it is impossible to then see the individual original members. Here is what I ended up doing.

1. First I used the same script that I was using to make my calculation in a mdx query and ensured I got the dates I expected.

Select
    {Measures.[Gross Units]} on 0,

  Exists(
    [Date].[Date].[Date].members,
      {
        [Date].[F Day Of Week].[F Day Of Week].item(0) :
        Exists([Date].[F Day Of Week].[F Day Of Week].members, [Date].[Last Complete Day].&[Y]).item(0)
      }
    * Exists([Date].[F Year].[F Year].members,[Date].[Last Complete Day].&[Y]).item(0).lag(1)
    * Exists([Date].[F Day of Year].[F Day of Year].members, [Date].[Current Week].&[Current Week])
  )


on 1
From myips

Results:

Gross Units 2015/05/09 (null) 2015/05/10 13,069 2015/05/11 35,853 2015/05/12 104,617 2015/05/13 46,318

2. Then I looked for the results, using my calculation for last year week to date and comparing that to a query scoped calculated member aggregated of those days

With Member [Date].[Fiscal].[Custom] as 
    Aggregate([Date].[Fiscal].[Date].&[2015-05-09T00:00:00]:[Date].[Fiscal].[Date].&[2015-05-13T00:00:00])

Select

[Date].[Fiscal].[Custom]
//Date.Fiscal.[LY WTD]
    on 0,

Measures.[Gross Units]
on 1
From myIPS

 **I got the same results! Success!**