0845 643 64 63

MDX Calculated Member Spanning Multiple Date Dimensions

It’s common in most cubes to have a number of different date dimensions, whether role playing, distinct, or a combination of both. Say for example, Entry Date, Posting Date and Accounting Period. There may also be numerous hierarchies in each date dimension, such as calendar and fiscal calendar, leading to a relatively complicated array of dates to worry about when calculating semi-additive measures.

If we create a date related calculation (i.e. total to date) how do we ensure that this calculation works across all date dimensions?

Lets assume we have a stock movement measure, where each record in the fact table is the change in stock (plus or minus). The current stock level is found by using a calculation totaling every record to date.

CREATE MEMBER CURRENTCUBE.[Measures].[Stock Level]
AS
  SUM({NULL:[Date].[Calendar].CurrentMember}
     , [Measures].[Stock Movement]
  );

[Note that {NULL:xxx} just creates a set of everything before the xxx member, i.e. everything to date]

This works just fine, if the user selects the [Date].[Calendar] hierarchy. What if the user selects the [Date].[Fiscal] hierarchy, or the [Period] dimension? Basically the calculation wont work, as the MDX expression is only aware of the [Date].[Calendar] hierarchy.

The simple solution is to use the Aggregate function over all of the dimensions that the calculation needs to be aware of:

CREATE MEMBER CURRENTCUBE.[Measures].[Stock Level]
AS
  AGGREGATE(
      {NULL:[Date].[Fiscal].CurrentMember}
       * {NULL:[Date].[Calendar].CurrentMember}
       * {NULL:[Period].[Period].CurrentMember}
    , [Measures].[Stock Movement]
  );

The calculation will then use whichever date or time hierarchy is selected. It will even cope if multiple dimensions are selected, say the calendar on 0 and the periods on 1, both axis will honor the aggregation as expected.

Frog-Blog out.

220 Responses to MDX Calculated Member Spanning Multiple Date Dimensions

  • I like this approach but really need to tweak it a bit. The need is to sum a balance sheet item like Mortgage Amount which is stored in the fact table as an amount per accounting period. I need to sum from beginning of time to the member from the level selected in one hierachy. Something like this:

    Calculate;

    CREATE MEMBER CURRENTCUBE.[Measures].[UDAGPTDNullGuy]
    AS AGGREGATE(PeriodsToDate(
    {NULL:[Account Period Filters].[Fiscal TA Hierarchy].[Fiscal Year TA],
    [Account Period Filters].[Fiscal TA Hierarchy].CurrentMember}
    *{NULL:[Account Period Filters].[Fiscal TA Hierarchy].[Fiscal Quarter TA],
    [Account Period Filters].[Fiscal TA Hierarchy].CurrentMember}
    *{NULL:[Account Period Filters].[Fiscal TA Hierarchy].[Account Period TA],
    [Account Period Filters].[Fiscal TA Hierarchy].CurrentMember}
    , [Measures].[UDAG Number])
    ),
    FORMAT_STRING = “$#,##0.00;($#,##0.00)”,
    VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = ‘Property Measures’ ;

    Any suggestions?

    • Hi Chris

      It looks like you’re overcomplicating this a little – all three attributes that you want to aggregate by (Year, Quarter and Period) are in the same [Fiscal TA Hierarchy], so you don’t need to worry about aggregating across multiple hierarchies. Instead, you can just sum from NULL to the current member of the hierarchy, which will automatically take care of the selected level (year, qtr, period).

      Try something like this

      CREATE
      MEMBER CURRENTCUBE.[Measures].[UDAGPTDNullGuy] AS
      Aggregate
      (
      {NULL : [Account Period Filters].[Fiscal TA Hierarchy].CurrentMember}
      ,[Measures].[UDAG Number]
      )
      ,FORMAT_STRING = “$#,##0.00;($#,##0.00)”
      ,VISIBLE = 1
      ,ASSOCIATED_MEASURE_GROUP = ‘Property Measures’ ;

      Let me know if this doesn’t do what you want.
      Alex

      • Hi Alex,

        I’ve wrote something like this to get the amount from starting to the current selected date.

        CREATE
        MEMBER CURRENTCUBE.[Measures].[AmountCur] AS
        Aggregate
        (
        {NULL : [[Time].[Years Quarters Months Weeks Days]CurrentMember}
        ,[Measures].[AmountCur])
        ,FORMAT_STRING = “$#,##0.00;($#,##0.00)”
        ,VISIBLE = 1
        ;

        But the above is not worked for me. Can you help me in this.

        Thanks,
        Rakesh

  • Excellent – works a treat, I was thinking you’d need a calculation for each hierarchy in the time dimension !

  • How would one handle this using a DateTool for comparison/aggregation calculations across multiple time dimensions?

    SCOPE([DateTool].[Aggregation].[Year To Date]);
    THIS =
    AGGREGATE(
    YTD(
    NULL:[Calendar Production].[Prod – Year – Qtr – Mth].CurrentMember *
    NULL:[Calendar Accounting].[Acct – Year – Qtr – Mth].CurrentMember)
    , [DateTool].[Aggregation].DefaultMember
    );

    END SCOPE;

    Produces a #VALUE! as the YTD function expects a member expression, and the NULL causes it to be considered a tuple.

    Thanks!

    • Hi Nick
      If you’re using YTD you wouldn’t need to use {NULL:xxx} as {NULL:xxx} returns a set of history to date. YTD takes a single member and returns you a set of year to date.
      You should be able to use
      AGGREGATE(
      YTD([Calendar Production].[Prod – Year – Qtr – Mth].CurrentMember) *
      YTD([Calendar Accounting].[Acct – Year – Qtr – Mth].CurrentMember)
      , [DateTool].[Aggregation].DefaultMember)

      Hope this helps – let me know if you get any problems
      Alex

      • Alex-
        When I use the YTD method you described, I seem to only be able to see values if I have both dimensions present in the query (either row or column). What I’d like to do is have it work even if I’m only looking at one dimension.

        I have an Activity Date and an Invoice Date. If someone is looking at just invoice date, it should just work. Instead I get Nulls. Any ideas?

        James

        • Hi James

          There should be no need to select members in both dimensions, so long as they both default to the [All] member. If they don’t then the code wont work and you’ll need to manually select a member in both.

          Hope this helps
          Alex

          • Alex
            I’ve pillaged your code and have got 90% of it to work perfectly, thank you!

            I’m using a utility dimension and trying to set up a YTD calc that spans multiple date hierarchies. I can get it to work fine with one date dimension, but as soon as I add a second date dimension the results come up as ‘NULL’ unless I select a member from both date dimensions.

            Here’s my code:

            AGGREGATE(
            YTD([Date1].[Date].CurrentMember) * YTD([Date2].[Date].CurrentMember),
            [Measures].[Count])

            I’ve also tried with ‘PeriodToDate’ and it does the same thing, also when I run this directly as MDX against the cube.

            The base date dimension is set up to default to ‘all’. I have another calculation on the utility dimension that does a simple cumulative calc, using AGGREGATE and {NULL:[Date].[Date].CURRENTMEMBER}, and this works fine across all our date dimensions.

            If you have a bright idea I’d be very grateful!

            Many thanks
            Tim

          • Hi Tim

            The problem that you’ve got is that YTD doesn’t make sense with the All member. YTD and PeriodToDate need a member selected within the year or period. i.e. with YTD(All), which year would it select?

            You should find the last descendant of the current member, and then pass that into YTD. Something like this:

            YTD(TAIL(DESCENDANTS([Date1].[Date].CurrentMember,,LEAVES),1).item(0))

            Then, if All is the current member, the last member will be selected and YTD will have something to go on.

            Hope this helps,
            Alex

  • Hi Alex,
    Your examples have been really helpful, I was wondering if you could help out a little more.
    In this case I’m trying to do an aggreate based on the Previous 30days of the current member
    I’m using a Date-Time Hierarchy, so the current member could be a year, quarter, month, day.. so I’d likely be looking at 30days from the lowest value in the current member.

    Right now I have one aggretation that does:
    Aggregate
    (
    {[End Date].[Date-Time Tree].CurrentMember:NULL }
    ,[Measures].[MembersCount]
    )
    — Though I’d really like to include a ‘[Begin Date] is less than current member as well to avoid counting records that haven’t ‘begun’ yet.

    In the end, I’d like to be able to look at a time line and see at any point in time, how many records were ‘current’ (their end date) is on or after that point in time, -30 days past due, -60 days past due and -90 days past due… I figure once I get the 30 it’s just lather, rinse and repeat for any other values I want.

    really hope you can help,
    Tony

    • Hi Tony

      The first stage would be to identify the leaf member of the hierarchy as the starting point, I.e find the date from whichever level is selected in the hierarchy.

      DESCENDANTS([End Date].[Date-Time Tree].CurrentMember,,LEAVES).item(0)

      You can then use lag to find the previous 30 members and build a set:

      {DESCENDANTS([End Date].[Date-Time Tree].CurrentMember,,LEAVES).item(0).lag(30)
      :DESCENDANTS([End Date].[Date-Time Tree].CurrentMember,,LEAVES).item(0)}

      Which you can then put into any calculated member.

      Hope this helps
      Alex

      • Alex,
        Thank you so much! that just about did it. My numbers are slightly off, but I suspects that’s because I need to use a DistinctCount.

        Thank you again for your help!
        Tony

  • Hi Alex

    Many thanks for your response! I think I’ve confused things… I meant to convey that I’ve not set up any user-defined defaults on my date dimensions, so I don’t think I’m falling foul of the point you raised re: James’ post above, about having custom default members.

    I’m basically trying to follow exactly the approach you described earlier in the post:

    AGGREGATE(
    YTD([Calendar Production].[Prod – Year – Qtr – Mth].CurrentMember) *
    YTD([Calendar Accounting].[Acct – Year – Qtr – Mth].CurrentMember)
    , [DateTool].[Aggregation].DefaultMember)

    I’m trying to get this working in principle in MDX, before I add it to the utility dimension. Accordingly, I’ve tested the following:

    WITH
    MEMBER Test1 AS
    AGGREGATE(YTD([Date1].[Date].CurrentMember),[Measures].[Count])
    MEMBER Test2 AS
    AGGREGATE(YTD([Date1].[Date].CurrentMember)*YTD([Date2].[Date].CurrentMember),[Measures].[Count])
    MEMBER Test3 AS
    AGGREGATE(
    YTD(TAIL(DESCENDANTS([Date1].[Date].CurrentMember),,LEAVES),1).Item(0))
    * YTD(TAIL(DESCENDANTS([Date2].[Date].CurrentMember),,LEAVES),1).Item(0))
    ,[Measures].[Count])

    SELECT {[Test1],[Test2],[Test3]} ON 0, [Date1].[Date].[Month].MEMBERS ON 1 FROM [Cube]

    Test1 works fine, Test2 and Test3 both return Null.

    Forgive me if I’m missing something very obvious here – anything you can suggest would be really useful!

    thanks
    Tim

    • Hi Tim

      Custom default members or not, you still need to pass a valid member into the YTD function. If you haven’t set a custom default, then the default will be All. You can’t do YTD(All), it just doesn’t make sense. You therefore need to ensure that a valid member is always presented to the YTD function, whether the user has selected a member of the date hierarchy or not.

      The tail(descentants(… code should ensure that a valid member is always presented. Can you try it and see if it works?

  • Alex,
    Thanks for this post. Using your approach I’m trying to accomplish the following:

    I’ve the following calculated member to count returning clients (clients who ordered from beginning to previous period) :

    CREATE MEMBER CURRENTCUBE.MEASURES.[Returning Clients] AS
    DISTINCTCOUNT
    (
    NONEMPTY(
    NONEMPTY([Client].[Client ID].[Client ID].members, [Measures].[Charge Amount])
    , {[Measures].[Charge Amount]} * {
    NULL : [Accession Date].[Year Qtr Month Date].CURRENTMEMBER.PREVMEMBER}
    )
    )

    As you can see I’m using the [Accession Date].[Year Qtr Month Date] hierarchy.
    I need the above calculation to work with another hierarchy([Accession Date].[Week End Date]) that I have.

    Thanks,
    AJ

    • Hi AJ

      The technique described here only works when using the CURRENTMEMBER of the date hierarchies, unfortunately using PREVMEMBER complicates matters somewhat.

      The problem you have is that, if the attribute relationships are set up correctly, selecting a week in the week hierarchy will also select the related year in the month hierarchy. Therefore PREVMEMBER applies to both hierarchies to find the cross join of the previous week AND the previous year.

      The easiest way around this is to first of all create a calculation which uses CURRENTMEMBER of both hierarchies (you can hide this). Then another calculated member which calls this with the PREVMEMBER of the selected hierarchy. You can determine which hierarchy to use by looking at the LEVEL.ORDINAL property of the CURRENTMEMBER of each of the hierarchies.

      Something like this…

      CREATE MEMBER CURRENTCUBE.MEASURES.[_Returning Clients] AS
      DISTINCTCOUNT((
      [Client].[Client ID].[Client ID].members
      , ([Measures].[Charge Amount]
      ,{NULL:[Accession Date].[Year Qtr Month Date].CURRENTMEMBER}
      *{NULL:[Accession Date].[Week End Date].CURRENTMEMBER}
      )
      )
      )
      , VISIBLE = 0;
      CREATE MEMBER CURRENTCUBE.MEASURES.[Returning Clients] AS
      IIF([Accession Date].[Year Qtr Month Date].CURRENTMEMBER.LEVEL.Ordinal
      > [Accession Date].[Week End Date].CURRENTMEMBER.LEVEL.Ordinal,
      ([Measures].[_Returning Clients]
      , [Accession Date].[Year Qtr Month Date].PREVMEMBER)
      ,([Measures].[_Returning Clients]
      , [Accession Date].[Week End Date].PREVMEMBER)
      )
      )

      Also note that you don’t need to use NONEMPTY. NONEMPTY is a display function, not a calculation function. the DISTINCTCOUNT will only count the non empty values so you don’t need to specify it. You only need to use NONEMPTY if you want to stop empty members from being displayed in the results.

      Good luck with this, hope it helps
      Alex

  • Alex,

    I need to write a query that would do the following:

    Return YTD, QTD, MTD values for an accounting year dimension based a selected reporting period dimension, also the query should return the closing period values and opening period values. How would go about constructing this query.
    Thanks in advance for your help

    Pierre

    • Hi Pierre

      The code will depend on the relationship between the accounting date hierarchy and the reporting period hierarchy. In a simple example, assuming they are the same (e.g. a single date hierarchy of Date, Period, Qtr, Year), you’d use the PERIODTODATE function to calculate the values for you, something like this…

      (please note that I’ve just written this freestyle, and not run or tested it, so don’t expect it to work straight off!)

      WITH MEMBER [Measures].[Year To Date] AS
      SUM(
      PERIODSTODATE([Date].[Date Hierarchy].[Year],
      [Date].[Date Hierarchy].CurrentMember)
      , [Measures].[Value])

      MEMBER [Measures].[Qtr To Date] AS
      SUM(
      PERIODSTODATE([Date].[Date Hierarchy].[Qtr],
      [Date].[Date Hierarchy].CurrentMember)
      , [Measures].[Value])

      MEMBER [Measures].[Period To Date] AS
      SUM(
      PERIODSTODATE([Date].[Date Hierarchy].[Period],
      [Date].[Date Hierarchy].CurrentMember)
      , [Measures].[Value])

      MEMBER [Measures].[Period Opening] AS
      ([Measures].[Period To Date]
      ,[Date].[Date Hierarchy].[Period].PREVMEMBER)

      MEMBER [Measures].[Period Closing] AS
      [Measures].[Period To Date]

      SELECT {[Measures].[Year To Date]
      , [Measures].[Qtr To Date]
      , [Measures].[Period To Date]
      , [Measures].[Period Opening]
      , [Measures].[Period Closing]
      } ON 0,
      [Date].[Date Hierarchy].[Period].MEMBERS ON 1
      FROM MyCube

      Hope this helps
      Alex

  • Hi Alex
    I have one Calender year Hiararchy in my project and I want to write an MDX which will do the aggregations over a period of time like from 1/15/2000 till 4/15/2010. Since its an aggregation over a period of 10 years. I want to be able to write an MDX where it pulls data out on daily basis for the month of Jan,2000 since it starts on the 15 and goes till 30 and then monthly from Feb,2000 till 1/1/2001 and then yearly onwards. Since the start and end dates will be changing, how can I write an MDX where it decides by itself where to pull the data from. Like if the start date is 1/1/2010 till 1/1/2012. It will know that it has to pull two records at the yearly level. Would really appreciate your help.
    Thanks
    -Sarah

    • Hi Sarah
      You don’t need to worry about whether to pull the day/month/year data out, the cube engine will take care of that for you. You just need to ask for the date range and the cube will automatically determine the optimal aggregations to use to satisfy the query.
      MDX is easier that most people think in this regard – let SSAS do the hard work for you!
      Alex

  • Nice approach, though it seems you would get a performance hit with the cross joining. Have you tested performance, comparing against other methods of producing similar results ?

    • That is a very good point which I should have pointed out. Yes you’ll get better performance using SCOPE and only referring to a single date hierarchy, however SCOPE is a more complex approach which stumps most MDX beginners, so this method provides a more simple alternative. I should also say that I haven’t run timing tests on this, however I would expect the performance hit to be minimal, as one of the date hierarchies will always be [All] (you’d only chose the month or week hierarchy, not both), therefore you’re not cross joining two large sets.
      Alex

  • Hi Alex,
    I am having Time Period Dimension which is having Program Year attribute which have members like 0-12 Months, 13-24 Months and 25-36 Months. Time Period is having Default Member 0-12 Months which is my requirement. So for Other Dimensions across all measures it shows 0-12 Months value. But two of my measures have requirement that Across all other dimension it should show All Time Period Value. How this is possible. Please suggest. Please note my two measures are distinct count measures.

    Thanks.

  • Hi Alex,
    I am having Time Period Dimension which is having Program Year attribute which have members like 0-12 Months, 13-24 Months and 25-36 Months. Time Period is having Default Member 0-12 Months which is my requirement. So for Other Dimensions across all measures it shows 0-12 Months value. But two of my measures have requirement that Across all other dimension it should show All Time Period Value. How this is possible. Please note my two measures are distinct count Measures. Please suggest

    Thanks.

  • Hi Alex,

    after some days struggling around with different hierarchies in my time dimension I found your post, this saved my day 🙂 closing period value solved, but…

    is there a similar approach for opening balance calculation as well? If you could propose a solution based on your example it would be very helpful.

    Thanks,
    Gregor

  • Hi Alex,

    I want to show one my dimensions All Level Member to be NULL. I am using scope to do so but as other dimenions will treat this all level member to show that measure. So it shows it null. So is it there any way for only showing purpose I make that all level member to be null but while browsing across all other dimension it will consider its actual all level value to be non null.

    please suggest.

    Thanks,
    Abhijeet

  • Hi Alex,
    I have tried all of your suggestions but to no avail!!!!
    Can you please try to help Me!!!
    I currently have 3 role playing date dimensions on my cube, StartDate, EndDate and ApprovalDate.
    I am trying to make a calculated member, that obbeys to filter on the three dimensions, and returns a sum of the number of active projects.
    I have been able to make the query work on SQL, but have no idea on how to implement that on MDX.
    The SQL Query resambles this:
    SELECT D.YearInt,D.MonthNumber,D.MonthNameChar
    ,COUNT(DISTINCT ProjectID) as NProjet
    FROM [FactProjects] FP ,[DimDate] D
    where
    (FinishDateProject)<='20130301'
    and (StartDateProject))<='20130301'
    and(D.YearInt=2013 and D.MonthNumber<=03)
    GROUP BY D.YearInt,D.MonthNumber,D.MonthNameChar
    order by D.MonthNumber

    This returns a query with this format:
    Year MonthNumber MonthName NProject
    2012 1 January 351
    2012 2 Fevereiro 351
    2012 3 Março 362
    So these are the total of active projects on each month for the current Year and month Filtered.
    So this means i will have a total for each month based on the start and finish date.
    Please Suggest!
    Thank You!
    Ricardo

    • Hi Ricardo,

      This is an interesting one, which I’ll answer in two stages.

      1) This is a data modelling problem, not an MDX problem. The technically correct way of approaching this would be to transform your data in your ETL into a structure more conducive to this style of reporting. If you’re going to SQL Bits in the UK in May then I’ll be presenting a talk on exactly this, how to remodel your data to better suit cubes and MDX. The theory is that with correct data modelling the MDX should be simple.

      2) Assuming that you don’t have the luxury of redesigning your warehouse or cube, we’ll have to tackle it in MDX. This is a different problem to the original focus of the post, but it can be tackled in a similar way, but with a few crucial changes.

      You want to filter your projects to those with a start date before the selected date, and with an end date after the selected date. Lets take each part in turn.

      All projects which start after a particular date (assuming your hierarchy in your date dimension is called ‘calendar’):
      AGGREGATE({[Start Date].[Calendar].[20130301]:NULL}, [Measures].[Project Count])
      This creates a set of all start dates after that selected, and then sums the project count.

      All projects which end before a particular date:
      AGGREGATE({NULL:[End Date].[Calendar].[20130301]:NULL}, [Measures].[Project Count])

      Combine them together, to get all projects which start after, and end before the specified date:
      AGGREGATE( {[Start Date].[Calendar].[20130301]:NULL}
      * {NULL:[End Date].[Calendar].[20130301]:NULL}
      , [Measures].[Project Count])
      This basically finds the intersection of the two sets, and returns the project count.

      Then you have the problem that you don’t want the user to choose a start date and an end date. You want them to select a single value from the 3rd [Date] dimension, and use that to apply to the above calculation.
      This is where we get to use an awesome function, LINKMEMBER. It links a member of one role playing dimension and maps it onto a different dimension.

      AGGREGATE( {LINKMEMBER([Date].[Calendar].CURRENTMEMBER, [Start Date].[Calendar]):NULL}
      * {NULL:LINKMEMBER([Date].[Calendar].CURRENTMEMBER, [End Date].[Calendar]):NULL}
      , [Measures].[Project Count])

      This takes the current member of the Date dimension, maps it to the start and end date dimensions, uses those to find the relevant project count.

      Alex

      • Hi Alex,
        Thank you so much for your answer, it solved my problem.
        I wish i coul attend SQL Bits in the UK but that won’t be possible.
        Can you suggest me some reading on trying to find a better way to modelling the data on this exact problem??

        Thanks
        Ricardo Santos

        • Hi Ricardo,

          Probably the best resource of generic data modelling learning is from Ralph Kimball himself, he does a number of data modelling courses. Any book by Ralph will be worth reading.
          http://www.kimballgroup.com/data-warehouse-and-business-intelligence-courses/

          For this exact problem I’d look at changing the fact table so that each project is stored twice, and only a single date dimension. One row would store the project start date, and +1 as a project count. The second row would store the end date, and -1 as the project count.

          You can then just sum the project count from {NULL:[Date].[Calendar].CURRENTMEMBER} to get the number of projects active on the selected date. You can also sum the project count for a single day, month, etc. to get the net change in the number of projects over that period. You get quite a lot of reporting flexibility by modelling the data as events instead of items. i.e. think of a project not as a single item, but as two distinct events; start and end.

          The right solution will depend on your reporting requirements, but this may be a good starting point, which would certainly simplify your MDX.

          Good luck with it
          Alex

    • Hi Ricardo

      Hope you don’t mind, I’ve taken your question and used it as the basis of a new blog post

      MDX Between Start Date and End Date

      Alex

      • Hi Alex,
        No problem whatsoever!!
        Now that i was able to solve it with your help and doing more and more of this type of queries, i hope i can be of some assistance!
        Thanks.
        Ricardo

  • Hi Alex,

    I’m struggling with two time-related dimensions: one is a hidden Date dimension with one hierarchy [Year > Quarter > Month] and the other is a Period dimension with one hierarchy [Custom aggregation period > Month] the content of which can be defined by the user (Excel import during ETL). The user can define custom aggregation periods (such as [Current year to date], [School year 2011-2012] etc.). The Period dimension is tied to the fact using a many-to-many relationship with an intermediate measure group period-date. The leaf level is month, but since a month can be used under multiple parents each month has its own key and (so multiple Jan-2013 members will have a different key in the Period dimension).

    This setup has been working for quite some time, but now I’m am running into trouble with some calculations. I intended to use the hidden Date dimension for calculations (with unique months) but I’m not sure how to identify the proper month member for the closing period of a given Period member.

    The closest I get to is the following for a year to date calculation (my example here is a simple sales cube for testing, but in reality the cube is for a social welfare organisation and my calculations involve various lead time calculations).

    WITH MEMBER [Measures].[Cumulative Sales]
    Sum(
    (
    Ytd(
    Tail(
    Filter(
    [Date].[Date].[Month].Members,
    Not IsEmpty([Measures].[Sales Count])
    )
    ).Item(0)
    ),
    [Period].[Period].DefaultMember
    ),
    [Measures].[Amount]
    )

    SELECT
    Descendants([Period].[Period].[2013 – M01 through M04]) ON 0,
    [Customer].Members ON 1
    FROM
    [My Test Cube]
    WHERE
    [Measures].[Cumulative Sales]

    This query does not return the proper results because it sums only correctly if the customer has sales in the closing period (M04 = April). Obviously this is something I impose explicitly by the Not IsEmpty([Measures].[Sales Count])

    My question in short is: how do I get the real month member (from the Date dimension for a given pseudo-month member of the Period dimension)?

    Your help would be greatly appreciated.

    Kind regards,
    Jan-Willem

    • Hi Alex,

      I have found a solution myself. To get the real month member in the Date dimension for a given pseudo-month member of the Period dimension, I use the following expression:

      NonEmpty(
      [Date].[Date].[Month].Members *
      ClosingPeriod(
      [Period].[Period].[Month],
      [Period].[Period].CurrentMember
      ),
      [Measures].[Intermediate Measure Group Row Count]
      ).Item(0).Item(0)} * [Period].[Period].DefaultMember

      Key is the use of the (typically hidden) measure from the intermediate measure group that is used for the many-to-many relationship. Furthermore, it is important to include the cross join with the [Period].[Period].DefaultMember. This ensures that the expression in which this construction is used, only depends on the Date dimension and not on the Period dimension. If, for example, the current member of Period is [School year 2012-2013] but the time intelligence should also cover months prior to August 2012 (= start of school year), the cross join will prevent the current member of the Period dimension from narrowing the scope of the calculation to the months August 2012 and later.

      A drawback of the cross join is that the Period attribute of the Period dimension should be set to be aggregatable, otherwise the DefaultMember would point to a member of the dimension that does not necessarily cover all months. Given the nature of the Period dimension, aggregation of periods (e.g. [Schoolyear 2012-2013] and [2012 – Q3]) is not useful.

      Do you have suggestions for improvement?

      Kind regards,
      Jan-Willem

      • Hi Jan-Willem

        Thanks for the follow-up post with a solution, sorry I’ve not had any time to look into this. I don’t have any sugegstions I’m afraid; with something as intricate as what you’re trying to do the only real way I’d be able to offer any advice would be to set up a working cube which mirrors your setup and then run a number of tests. This is a little too time consuming, in depth and specific to cover off in a blog question, and would require some consultancy time.

        Time for blogging is very limited at the moment I’m afraid! (as you’ll see from the low number of posts recently!!!)

        Regards
        Alex

  • Hi Alex,
    I have been stuck on Calculated Member Spanning Multiple Date Dimensions for a while now, and I’m starting to lose it :).
    Basically I have two role playing dimensions, selling and arrival (each have 1 different hierarchy)

    I’m using a Date Comparison shell dimension with the attributes regular, previous year, YTD.

    Below are a few examples of MDX I have tried. Any help would be greatly appreciated!!!!!

    Previous year (this works fine for date selling dimension, but I’m having no luck getting including date arrival dimension)

    SCOPE ([Date Selling].[Date].MEMBERS,[Date Comparison].[Comparison].[Previous Year]);
    this =
    (ParallelPeriod([Date Selling].[Calendar Year – Month – Date].[Calendar Year],1, [Date Selling].[Calendar Year – Month – Date].CurrentMember),
    [Date Comparison].[Comparison].DefaultMember);
    END SCOPE;

    YTD (again, works fine for 1 dimension)

    SCOPE (
    [Date Arrival].[Arrival Year – Month – Date].MEMBERS,
    [Date Arrival ].[Date].MEMBERS);
    ( [Date Comparison].[Comparison].[YTD] )
    = Aggregate(
    { [Date Comparison].[Comparison].DefaultMember } *
    PeriodsToDate(
    [Date Arrival].[Arrival Year – Month – Date].[Arrival Year],
    [Date Arrival].[Arrival Year – Month – Date].CurrentMember
    ) );
    END SCOPE;

    I have tried some examples based on your previous answers.. but this only returns #VALUE! e.g.

    SCOPE([Date Comparison].[Comparison].[YTD]);
    this =
    AGGREGATE(
    PeriodsToDate([Date Arrival].[Arrival Year – Month – Date].CurrentMember) *
    PeriodsToDate([Date Selling].[Calendar Year – Month – Date].CurrentMember)
    , [Date Comparison].[Comparison].DefaultMember);
    END SCOPE;

    • Hi Brad
      Could you send me a backup of the cube? Difficult to diagnose MDX scope problems without being able to run tests.
      Thanks
      Alex

      • Sure. How do I attach a file here?

        • Hi Alex and Brad,
          I think i have been trough a problem like this and i think i can help.
          Here is my small contribution.

          I also have a Calculated member spanning across multiple date hierarchy.
          I have used a method that although slower than other possible solutions, will work fine!

          Let me explain my scenario.
          I use a single date hierarchy to filter all my objects, but i have a calculated member that spans across four different date hierarchys.

          This is the method i used to be able to achive the calculated member.

          // Calculated member that returns projects that have already started, and have no
          // finished on a selectd date range
          CREATE MEMBER CURRENTCUBE.[Measures].[Running Projects]
          AS AGGREGATE(
          ((NULL:LinkMember( [Date].[Calendar].currentmember, [Start Date].[Calendar])) //Started project after selected date
          ,(LinkMember( [Date].[Calendar].currentmember, [End Date].[Calendar]) : NULL)) //Project that end after selected date
          ,[Measures].[Measure]);

          NOTE: i simplified the script to two single date hierarchys.

          Hope this helps!!!

          • Hi Ricardo, thanks for the suggestion

            LinkMember can solve a lot or problems, as can cross joining dimensions. I can’t test it to see what results it gives, but performance will always take a hit – always better to try and use scope where possible if performance is an issue.

    • Try something like this…

      SCOPE([Date Comparison].[Comparison].[YTD Test]);
      this = AGGREGATE(YTD([Ship Date].[Calendar].CurrentMember)
      ,[Date Comparison].[Comparison].DefaultMember);

      SCOPE([Ship Date].[Calendar].[All]);
      this = AGGREGATE(YTD([OrderDate].[Calendar].CurrentMember)
      ,[Date Comparison].[Comparison].DefaultMember);
      END SCOPE;
      END SCOPE;

      It calculates on the ship date, however if the [all] member of the ship date is being used then it will switch to using the order date.

      In the example cube you provided the Fiscal date hierarchy isn’t set up correctly so YTD wont work with it, so I’ve used the calendar hierarchy from both to make it work.

      • Hi Alex,

        Thanks for you answer. It looks to be what I’m after, however I’m still having trouble using the different hierarchies. You mention the fiscal date hierarchy is not setup properly. So maybe the hierarchies are my problem.

        Thanks

        • Couple of things to look at: YTD needs the attributes to be set up correctly, Fiscal Year is not set up as a year at the moment.
          Also the attribute hierarchy isn’t set up properly from date to month to fiscal year.

          • I see what you mean about the fiscal year… thanks.

            The solution you provided almost gives me what I need. However I need to have the possibilty to filter by order date and have ship date in rows and vice versa, filter by ship date and have order date in the rows. Using the example you provided, The YTD is only represented correctly with order date as filter and ship date in rows. With ship date as filter and order in rows, the regual value is shown as YTD
            Anyway this is really useful so far! Thanks

          • Hi Brad,
            If you have both hierarchies in the query then the cube has no way of knowing which one you want to filter by, so your best option is to have two separate measures.
            Alex

  • Hi Alex,

    I’m struggling with a problem which relates a bit to this subject. I’ll give it a try posting it. Hopefully I’ll get an answer which helps me further.

    In SSAS I am looking for a way to calculate a previous year total on (amongst others) the measure gewicht (weight). I have used the calculation script which I’ve copied below. I have a dimension time which has different levels (year, period – which is a 4 weeks period – and week).

    The selections made in the time dimension affect the results of the numbers represented in my pivot: when only a year is selected the calculation works fine (the year is used to determine the previous year of course). When apart from that a period (1…13) OR a week (1…53) is selected the results are also fine.

    But when I select a year and also a period AND a week, then the results are messed up.

    CREATE MEMBER CURRENTCUBE.[Measures].[Gewicht Totaal VJ]
    AS case
    when [Periode].[Week].CurrentMember.Level.Ordinal=1
    then sum(([Periode].[Jaar].Prevmember, [Periode].[Week].[1] : [Periode].[Week].[53]), [Measures].[Gewicht DJ])
    when [Periode].[Periode].CurrentMember.Level.Ordinal=1
    then sum(([Periode].[Jaar].Prevmember, [Periode].[Periode].[1] : [Periode].[Periode].[13]), [Measures].[Gewicht DJ])
    when [Periode].[Jaar].CurrentMember.Level.Ordinal=1
    then ([Periode].[Jaar].Prevmember, [Measures].[Gewicht DJ])
    end,
    FORMAT_STRING = “#,##0;-#,##0”,
    NON_EMPTY_BEHAVIOR = { [Measures].[Gewicht DJ]},
    VISIBLE = 1 , DISPLAY_FOLDER = ‘Totalen Vorig Jaar’ , ASSOCIATED_MEASURE_GROUP = ‘Verkopen’;

    Can you please help me out on how to adapt this script in such a way that the correct numbers are displayed regardless of the selections made in the time dimension? A year total is of course always the same…

    Thanks, your help is greatly appreciated!

    Barry

    • Hi Barry
      The problem is not the MDX, it’s what you’re trying to do. To calculate the previous year’s value, you need to have a single item selected. If you select both Period 1 and August, finding the previous year’s value doesn’t make any sense – they are two different values which can’t be shown in a single calculation.
      Maybe you could explain in business terms why you need to have a multi-select, and how you’d go about calculating it manually. Then we could look to define an MDX calculation that replicates it.
      Alex

      • Hi Alex,

        Thank you for your reply! I understand it seems a bit strange what I’m describing: making date filters at two different levels. It’s something we ran into accidentally, so it is in fact not a normal user scenario. However, I think that the calculation should still be able to deliver the correct result as it is a year total.

        Have a nice weekend!

        Barry

  • Hey Alex,

    I am really loving this thread but I am stuck on a part here. I see this part about being able to use two dates. I think this idea works for me. I have two dates one is Order Placed DAte and other is Status DAte. I would like it to default to Order Placed DAte and then if all is selected use Status Date. However I cannot figure out how to code this. Here is the code I have right now.

    CREATE MEMBER CURRENTCUBE.[Measures].[% Growth (Order Date)] AS
    //Checks for isempty and null condition sets them to null instead of #NUM!
    IIF (([Order Placed Date].[Order Placed Date].CurrentMember.PrevMember, [Measures].[Total Cost]) = 0
    ,NULL
    ,IIF (([Order Placed Date].[Order Placed Date].CurrentMember, [Measures].[Total Cost]) = 0
    ,NULL
    ,(([Order Placed Date].[Order Placed Date].CurrentMember, [Measures].[Total Cost])
    – ([Order Placed Date].[Order Placed Date].CurrentMember.PrevMember, [Measures].[Total Cost]))
    / ([Order Placed Date].[Order Placed Date].CurrentMember.PrevMember, [Measures].[Total Cost]))),
    FORMAT_STRING = “#,##0.00 %;-#,##0.00 %”,
    VISIBLE = 1, ASSOCIATED_MEASURE_GROUP = ‘Report’;

    Then a second one that shows same thing but for Status DAte. Thus instead of having two seperate calculations I would like to see 1 single calculation. How to I change the above so the Scope works with it.

    SCOPE([Date Comparison].[Comparison].[YTD Test]);
    this = AGGREGATE(YTD([Ship Date].[Calendar].CurrentMember)
    ,[Date Comparison].[Comparison].DefaultMember);

    SCOPE([Ship Date].[Calendar].[All]);
    this = AGGREGATE(YTD([OrderDate].[Calendar].CurrentMember)
    ,[Date Comparison].[Comparison].DefaultMember);
    END SCOPE;
    END SCOPE;

  • Hi Alex,
    I’m quite naive in MDX , I am trying to define a calculated member from an existing time hierarchy YWD , I need to populate the latest values of a month when month dimension is taken across column , currently I’m able to point to lastchild.lastchild on year ( eg: year 2010 week 5 day 31-01-2011) should return the value for last day of the month (31jan ) when taken across month dimension on columns , can you shread some light on this.. Thanks in advance

    • You can use the Tail function combined with Descendants, to find the last descendant of the current member of the date hierarchy.

      Tail(Descendants([Date].[YWD].Currentmember, [Date].[YWD].[Date]), 1)

      This returns a set with a single member, you can add .Item(0) if you want a member not a set.

      Alternatively there is a shortcut, using the ClosingPeriod function.

      ClosingPeriod(Date].[YWD].[Date], [Date].[YWD].Currentmember)

      Regards
      Alex

  • Hi Alex ,
    Thanx for the advice ,but Sorry that wasn’t my requirement. Let me try to make it much clearer this time 🙂

    I have created a calculated measure for date hierarchy YWD , which returns the latest value For each level by taking ordinal values in to account …
    Here is how it works
    Year 2011->
    Week 52 ->
    ->dec26 – dec31

    Query on year would return value at member dec31 , query on week 52 would return the same , hence latest across each week .

    It works fine on YTD dimension , Now how can I bring in month dimension across , say on column across above example . so that i cold get value of day dec31 across month dec , value of nov30 across month Nov (which in turn is in week 48) ??

    I don’t to what extend you got my scenario …
    Could you advice !!!!

  • Hi Alex ,

    As you have suggested , Can we populate the ClosingPeriod results on YWD hierarchy across month dimension ???. Could you please advice.

    • Hi
      This isn’t a straightforward question to answer, I’d need to see the cube structure and look at it in a lot more detail to determine the best approach – there’s a limit to how complex one can get in blog comments I’m afraid.
      For example it depends on the relationship between weeks and months (a week can span multiple months) etc.
      So it sounds like you need to get someone in to take a close look at it and offer appropriate advice.
      Good luck with it!
      Alex

      • Thanks for your quick response.
        I think it’s a simple cube with only week level on the fact table with start-schema design. Let me try your idea. If we have a big problems will offer you help on that.

        Many thanks,
        Thanh

  • Hi Alex,

    Sorry for posting a new question,

    I’m a beginner on SSAS Cube MDX, I have an urgent issue needs you advise.
    I have one dimension named: DimProduct and one FactStores have a measurement named: NumberOfUnits
    I want to write one MXD logic to set value for the NumberOfUnits
    if DimProductKey=1 then FactStores.NumberOfUnits= FactStores.CurrentValue of FactStores.NumberOfUnits else set NumberOfUnits =0)

    Any help would be appreciated!
    Thanh

    • Your best option is to use ‘scope’ to change the definition of the calculation depending on the scenario.
      In the Calculations tab of the cube designer, add something like the following:

      SCOPE([Measures].[NumberOfUnits]
      , EXCEPT([DimProduct].[DimProductKey].MEMBERS, [DimProduct].[DimProductKey].&[1]));
      THIS = NULL;
      END SCOPE;

      This will override the calculation for all values of DimProductKey other than 1, setting their values to null.
      Alternatively, rename the existing measure to [NumberOfUnits_Hidden] and hide it, then use scope to create a new measure

      CREATE MEMBER CURRENTCUBE.[Measures].[NumberOfUnits]
      AS NULL;
      SCOPE([Measures].[NumberOfUnits]);
      SCOPE([DimProduct].[DimProductKey].&[1]);
      THIS = [Measures].[NumberOfUnits_Hidden];
      END SCOPE;
      SCOPE([DimProduct].[DimProductKey].[All]);
      THIS = ([DimProduct].[DimProductKey].&[1]
      , [Measures].[NumberOfUnits_Hidden]);
      END SCOPE;
      END SCOPE;

      This allows you more control over how the measure is summarised, i.e. when the [All] member is selected, you can either manually aggregate the values of the children, or in this case you can just override [All] with the value for &[1], which will keep the calculation very fast.

      • Hi Alex,
        Your suggestion has done with small changes. Thank you very much! And I have another question need your help.
        I have a fact Fact_Orders has some measures as Ordered at week level. In the cube I want to create a derived column to keep previous value of ordered columns (same time grain)
        It means that the cube has to including measures as previous_ordered, ordered (current order).
        Please suggest me any idea!

        Regards,
        Thank

        • You can use the PrevMember of the date hierarchy to get the previous period’s measures in a calculated measure. Or use ParallelPeriod.

          There’s plenty of examples on blogs, including here:
          http://xzwang.wordpress.com/2013/08/13/a-better-way-to-write-mdx-period-over-period-calculations/

          Regards
          Alex

  • Thnx Alex ,much appreciated …

  • Hello Alex,

    I want to create running totals within my cube for one of the distinct count measure that I have
    And following table shows my measures and required running total measure values.

    “Unique Users” measure is distinct count measure of users.
    And “RunningUU” measure is just sums the numbers, but it should be distinct count of users.
    My goal is to create running values similar to “Actual Running Total should be” measure in the following list.

    Row Labels Unique Users RunningUU Actual Running Total Should be
    FY08 8658 8658 8658
    FY09 51175 59833 57000
    FY10 62914 122747 85000
    FY11 93702 216449 130000
    FY12 119264 335713 190000
    FY13 124156 459869 220000
    FY14 93493 553362 268549
    FY15 553362 268549
    Grand Total 268549 268549 268549

    Please let me know if anyone knows creating running totals for distinct count measures with time dimension.

    Appreciate your help

    • You can achieve this by just creating a new calculation that aggregates the Unique Users measure over the set of all historic time members.

      CREATE MEMBER CurrentCube.[Measures].[Running Total] AS
      AGGREGATE({null:[Date].[Calendar].CurrentMember}
      , [Measures].[Unique Users]);

      This calculates the Unique User measure over the set of all dates from the beginning of time (null) through to the current selected member of the Calendar hierarchy in the Date dimension.

  • Good Evening Alex.
    I am pretty new in SSAS 2008 R2.
    I have a cube with a [Measures].[Sales Amount] and a Time Dimension [Calendar]
    In Time Dimension i have a Hierarchy like this: Year (2014) – Week – Date (ie 15 June 2014)
    I have also other hierarchies…such as YEAR-MONTH-DAYS .. and so on

    My work collegues need to compare sales amount from 1 June 2014 to 14 June 2014
    w i t h
    parallel period of pevious year but NOT from 1 June 2013 to 14 June 2013
    B u t
    parallel period of previous year according the same type of days (MOnday with monday ,,,tuesday with tuesday and so on,…)
    In my case i need to calculate from 2 June 2013 to 15 June 2013..

    Is it possibile in SSAS to do that ???
    I need other kind of hierarchies in my TIME DIMENSIONS ???

    I think i need to use hierarchy YEAR-WEEK-DATE but i am so expert to think to that..
    Any suggest or article to study ???

    Thanks a lot.

    • Yes you’re right, you could only do this with the Year-Week-Date hierarchy, it wouldn’t make any sense when using the Year-Month-Date hierarchy.
      Something like:

      (ParallelPeriod([Calendar].[Year-Week-Date].[Year],1,
      [Calendar].[Year-Week-Date].CurrentMember),[Measures].[Sales Amount])

      This does mean that this calculation will not work when using months.

  • Hi Alex,

    Thanks again for your valuable time.

    I would like to ask you a question regarding this post.

    I redesign my snapshots table in order to have just one date for the open status of the orders using a view. Now I followed your suggestions to make it works in to dimension, something like this:

    CREATE MEMBER CURRENTCUBE.[Measures].[OOH Net Value]
    AS aggregate(
    {null:[Time].[Year – Week – Date].CurrentMember} * {null:[Planned Deliv Date].[Year – Week – Date].CurrentMember}
    ,[Measures].[OOH Net Value Domestic]);

    It works fine, then next challenge is to calculate the Last Year Value. I did it like this:

    CREATE MEMBER CURRENTCUBE.[Measures].[OOH Net LY] AS NULL;

    SCOPE([Measures].[OOH Net LY]);
    SCOPE([Planned Deliv Date].[Date].Members, [Time].[Date].Members);
    this =
    — Determine the time dimension to be used
    iif([Planned Deliv Date].[Year – Week – Date].CurrentMember.Name = [Time].[Year – Week – Date].[AllDates].Name,
    (
    Cousin
    (
    [Time].[Year – Week – Date].CurrentMember
    ,Ancestor
    (
    [Time].[Year – Week – Date].CurrentMember
    ,[Time].[Year – Week – Date].[Year]
    ).Lag(1)
    )
    ,[Measures].[OOH Net Value]
    (
    Cousin
    (
    [Nat Planned Sales Deliv Date].[Year – Week – Date].CurrentMember
    ,Ancestor
    (
    [Nat Planned Sales Deliv Date].[Year – Week – Date].CurrentMember
    ,[Nat Planned Sales Deliv Date].[Year – Week – Date].[Year]
    ).Lag(1)
    )
    ,[Measures].[OOH Net Value]
    )

    ); — end if and this
    END SCOPE;
    END SCOPE;

    As you can see, the code has too many lines, it is repetitive and not especially easy to understand. Basically what I do is:
    – Define an AllMemberName for my Time hierarchy [Year – Week -Date] (AllDates)
    – Determine which is the selected hierarchy
    – Calculate the Last Year value using the Cousin function

    The script works, but I am not happy with it, so I summarized the requirements here:
    – The script should works with both time dimensions
    – Across every hierarchy (I have more than one)
    – Only snapshots up to the current date should be shown (I already solved this requierement but I have not included to “simplify” the script)

    May you help me to determine other possible solution?

    Any comment would be appreciated.

    Kind Regards,

    Paul

  • Hi Alex,
    I have different time dimensions… start time of the service, end time of the service and the time for the report.
    I need to calculate:
    Start time less or equal to currenttime AND ( End time is null OR greater then current time) for a measure?
    I’m French speaking, so sorry for any spelling mistake

    • You can use something line this

      SELECT {[Measures].[My Measure]} ON 0,
      NON EMPTY {
      [Service].[Service].[Service].MEMBERS
      * {null:LINKMEMBER([Report Date].[Calendar].CURRENTMEMBER, [Service Start Date].[Calendar])}
      * {LINKMEMBER([Report Date].[Calendar].CURRENTMEMBER, [Service End Date].[Calendar]):null}
      }
      ON 1
      FROM [My Cube]
      WHERE [Report Date].[Calendar].[Date].&[20140722]

      Where you filter by a ‘Report Date’ dimension, which isn’t actually linked to the measure group. However you can use LinkMember to map the Report Date into the Service Start and Service End date dimensions.
      Use this to create sets of dates for all start dates prior the report date {null:xx} and all end dates after the report dates {xxx:null}.
      Cross Join them together and you will get a set of Service members matching the dates.
      Regards
      Alex

  • Dear Alex,

    I have read through all the questions posed here and I am really impressed by your answers. Please can you help me in my situation, I am still learning OLAP cube and MDX. My scenario is as follows:

    I have 2 date dimension hierarchies –

    the first – Year-Quarter-Month-Week-Day
    the second Year-Quarter-Month-Day

    I needed to allows my user to select a date or range of date from a visual tool and then display the result as follows (this is just a subset of the fields):

    Sales Amt, MTD Sales, YTD Sales, Prior Yr MTD Sales, Prior Yr YTD Sales

    Now, lets say my user select 20th as their date filter for a month that has the full month data already, the expected result is MTD Sales for the first 20 day of the month, sames for the other aggregations.

    Using the second date dimension hierarchy, I am able to achieve that. Using the first date dimension hierarchy, the result for MTD Sales is the total sales for the month and not the first 20 days as desired. Since the second date dimension hierarchy work for this, I am fine.

    However, when user select a range of date, say 1st – 20th, then I am unable to get MTD Sales for the first 20 days, either with the first date dimension hierarchy or the second. Actually, I get error when I use the second dimension hierarchy, and get full month MTD Sales with the first dimension hierarchy.

    Please what can I tweak to achieve my goal. Thanks in anticipation.

    Gboyega

  • I Have 4 date dimensions with defined hierarchy . They all have different names so I cannot use the NULL.

    I tried using the Scope method you described above and it works fine for some and not for others especially the YTD and prior YTD calculations. My YTD only shows last years month data and the Prior YTD shows previous month data. Not sure what I am doing wrong.

    I created a shell date calculation dimension based on this url

    http://saldeloera.wordpress.com/2013/02/07/ssas-how-to-create-dynamic-time-calculations-to-reuse-over-any-measure/

    Here is part of my script

    // YTD calculations

    SCOPE([Date Calculation].[Date Calculation].MEMBERS);
    [Date Calculation].[Date Calculation].[YTD] =

    AGGREGATE
    (
    {[Date Calculation].[Date Calculation].[Selected Date]} *
    PERIODSTODATE
    (
    [S Date].[S Date Yr].[S Date Yr],
    [S Date].[S Date Yr].currentmember
    )
    );

    SCOPE ([S Date].[S Date Yr].[All]);

    [Date Calculation].[Date Calculation].[YTD] = AGGREGATE (
    {[Date Calculation].[Date Calculation].[Selected Date]} *
    PERIODSTODATE
    (
    [E DATE].[E Date Yr].[E Date Yr],
    [E DATE].[E Date Yr].currentmember
    )
    );

    SCOPE ([E DATE].[E Date Yr].[All]);
    [Date Calculation].[Date Calculation].[YTD] =

    AGGREGATE (
    {[Date Calculation].[Date Calculation].[Selected Date]} *
    PERIODSTODATE
    (
    [T DATE].[T Date Yr].[T Date Yr],
    [T DATE].[T Date Yr].currentmember
    )
    );

    SCOPE ([T DATE].[T Date Yr].[All]);

    [Date Calculation].[Date Calculation].[YTD] = AGGREGATE (
    {[Date Calculation].[Date Calculation].[Selected Date]} *
    PERIODSTODATE
    (
    [P DATE].[P Date Yr].[P Date Yr],
    [P DATE].[P Date Yr].currentmember
    )
    );

    END SCOPE;
    END SCOPE;
    END SCOPE;
    END SCOPE;

    SCOPE([Date Calculation].[Date Calculation].MEMBERS);
    [Date Calculation].[Date Calculation].[PY YTD] =

    IIF( AGGREGATE
    (
    {[Date Calculation].[Date Calculation].[Selected Date]} *
    PERIODSTODATE
    (
    [S Date].[S Date Yr].[S Date Yr],

    PARALLELPERIOD
    (
    [S Date].[S Date Yr].[S Date Yr],
    1,
    [S Date].[S Date Yr].CURRENTMEMBER
    )
    )
    ) = 0 OR ( AGGREGATE
    (
    {[Date Calculation].[Date Calculation].[Selected Date]} *
    PERIODSTODATE
    (
    [S Date].[S Date Yr].[S Date Yr],

    PARALLELPERIOD
    (
    [S Date].[S Date Yr].[S Date Yr],
    1,
    [S Date].[S Date Yr].CURRENTMEMBER
    )
    )
    ) ),

    AGGREGATE
    (
    {[Date Calculation].[Date Calculation].[Selected Date]} *
    PERIODSTODATE
    (
    [S Date].[S Date Mo Yr].[S Date Mo Yr],

    PARALLELPERIOD
    (
    [S Date].[S Date Mo Yr].[S Date Mo Yr],
    11,
    [S Date].[S Date Mo Yr].CURRENTMEMBER
    )
    )
    ),
    AGGREGATE
    (
    {[Date Calculation].[Date Calculation].[Selected Date]} *
    PERIODSTODATE
    (
    [S Date].[S Date Yr].[S Date Yr],

    PARALLELPERIOD
    (
    [S Date].[S Date Yr].[S Date Yr],
    1,
    [S Date].[S Date Yr].CURRENTMEMBER
    )
    )
    ));

    SCOPE([S Date].[S Date Yr].[All],
    [S Date].[S Date Mo Yr].[All]);

    [Date Calculation].[Date Calculation].[PY YTD] =

    IIF(
    AGGREGATE
    (
    {[Date Calculation].[Date Calculation].[Selected Date]} *
    PERIODSTODATE
    (
    [E DATE].[E Date Yr].[E Date Yr],

    PARALLELPERIOD
    (
    [E DATE].[E Date Yr].[E Date Yr],
    1,
    [E DATE].[E Date Yr].CURRENTMEMBER
    )
    )
    ) = 0 OR (AGGREGATE
    (
    {[Date Calculation].[Date Calculation].[Selected Date]} *
    PERIODSTODATE
    (
    [E DATE].[E Date Yr].[E Date Yr],

    PARALLELPERIOD
    (
    [E DATE].[E Date Yr].[E Date Yr],
    1,
    [E DATE].[E Date Yr].CURRENTMEMBER
    )
    )
    )),

    AGGREGATE
    (
    {[Date Calculation].[Date Calculation].[Selected Date]} *
    PERIODSTODATE
    (
    [E DATE].[E Date Mo Yr].[E Date Mo Yr],

    PARALLELPERIOD
    (
    [E DATE].[E Date Mo Yr].[E Date Mo Yr],
    11,
    [E DATE].[E Date Mo Yr].CURRENTMEMBER
    )
    )
    ),
    AGGREGATE
    (
    {[Date Calculation].[Date Calculation].[Selected Date]} *
    PERIODSTODATE
    (
    [E DATE].[E Date Yr].[E Date Yr],

    PARALLELPERIOD
    (
    [E DATE].[E Date Yr].[E Date Yr],
    1,
    [E DATE].[E Date Yr].CURRENTMEMBER
    )
    )
    ))

    ;
    SCOPE([E DATE].[E Date Yr].[All],
    [E DATE].[E Date Mo Yr].[All]);
    [Date Calculation].[Date Calculation].[PY YTD] =

    IIF(
    AGGREGATE
    (
    {[Date Calculation].[Date Calculation].[Selected Date]} *
    PERIODSTODATE
    (
    [T DATE].[T Date Yr].[T Date Yr],

    PARALLELPERIOD
    (
    [T DATE].[T Date Yr].[T Date Yr],
    11,
    [T DATE].[T Date Yr].CURRENTMEMBER
    )
    )
    ) = 0 OR IsEmpty(AGGREGATE
    (
    {[Date Calculation].[Date Calculation].[Selected Date]} *
    PERIODSTODATE
    (
    [T DATE].[T Date Yr].[T Date Yr],

    PARALLELPERIOD
    (
    [T DATE].[T Date Yr].[T Date Yr],
    11,
    [T DATE].[T Date Yr].CURRENTMEMBER
    )
    )
    )),

    AGGREGATE
    (
    {[Date Calculation].[Date Calculation].[Selected Date]} *
    PERIODSTODATE
    (
    [T DATE].[T Date Mo Yr].[T Date Mo Yr],

    PARALLELPERIOD
    (
    [T DATE].[T Date Mo Yr].[T Date Mo Yr],
    11,
    [T DATE].[T Date Mo Yr].CURRENTMEMBER
    )
    )
    ),

    AGGREGATE
    (
    {[Date Calculation].[Date Calculation].[Selected Date]} *
    PERIODSTODATE
    (
    [T DATE].[T Date Yr].[T Date Yr],

    PARALLELPERIOD
    (
    [T DATE].[T Date Yr].[T Date Yr],
    11,
    [T DATE].[T Date Yr].CURRENTMEMBER
    )
    )
    ) )
    ;
    SCOPE([T DATE].[T Date Yr].[All],
    [T DATE].[T Date Mo Yr].[All]);
    [Date Calculation].[Date Calculation].[PY YTD] =

    IIF(
    AGGREGATE
    (
    {[Date Calculation].[Date Calculation].[Selected Date]} *
    PERIODSTODATE
    (
    [P DATE].[P Date Yr].[P Date Yr],

    PARALLELPERIOD
    (
    [P DATE].[P Date Yr].[P Date Yr],
    1,
    [P DATE].[P Date Yr].CURRENTMEMBER
    )
    )
    ) = 0 OR IsEmpty(AGGREGATE
    (
    {[Date Calculation].[Date Calculation].[Selected Date]} *
    PERIODSTODATE
    (
    [P DATE].[P Date Yr].[P Date Yr],

    PARALLELPERIOD
    (
    [P DATE].[P Date Yr].[P Date Yr],
    1,
    [P DATE].[P Date Yr].CURRENTMEMBER
    )
    )
    ) ),

    (
    {[Date Calculation].[Date Calculation].[Selected Date]} *
    PERIODSTODATE
    (
    [P DATE].[P Date Mo Yr].[P Date Mo Yr],

    PARALLELPERIOD
    (
    [P DATE].[P Date Mo Yr].[P Date Mo Yr],
    11,
    [P DATE].[P Date Mo Yr].CURRENTMEMBER
    )
    )
    ),
    AGGREGATE
    (
    {[Date Calculation].[Date Calculation].[Selected Date]} *
    PERIODSTODATE
    (
    [P DATE].[P Date Yr].[P Date Yr],

    PARALLELPERIOD
    (
    [P DATE].[P Date Yr].[P Date Yr],
    1,
    [P DATE].[P Date Yr].CURRENTMEMBER
    )
    )
    ) )
    ;

    END SCOPE;
    END SCOPE;
    END SCOPE;
    END SCOPE;

    When the end user selects both [YR] and [MO YR] dates we can see the YTD and the PY YTD but this is only calculating monthly values. They want to be able to select either YR or MO YR or a combination of both and still get all YTD and PY YTD.

    What am I doing wrong?

    Thank you.

    When the end user selects a

    • Hi Tonia
      Thanks for the comment, however I’m afraid this is too in depth to be able to debug and diagnose this via a blog. I’d recommend that you engage an SSAS/MDX consultant to review this and investigate options. Quite often problems like this can be solved by alterations to the data model or cube structure, without having to do as much work in MDX. Where possible, this is always a preferable solution and hence why I’m reluctant to offer advice without understanding more details of the system.
      Hope this makes sense
      Regards
      Alex

  • Hi Alex,

    I’ve wrote something like below to the sum from starting to the selected date.

    CREATE MEMBER CURRENTCUBE.[Measures].[AmountCur]
    AS
    SUM({NULL:[Time].[Years Quarters Months Weeks Days].CurrentMember}
    , [Measures].[AmountCur]
    );

    But I’m not getting the correct result.

    Can you help me out here.

    Thanks,
    Rakesh

  • Hi Alex!
    A simple question from a newby:
    Is there a way to create a calculated member/measure which would be based on the set of the non null months in the Date hierarchy and the measure value? The DW was design in such way that the month attribute has null value in case the row belongs to quarter or year in the Date dimension. Like this:
    Id,Year,Quater,Month
    1,2014,null,null,
    2,2014,null,1
    Regards,
    Gabor

    • Hi Gabor
      I’m really not a fan of including NULLs in member values in a cube. Personally I’d use 0 as a value for Qtr/Month that don’t exist.

      Then the simplest option is to just deduct the 0 month from the overall total value:
      [Measures].[My Measure] – ([Measures].[My Measure], [Date].[Month].[0])

      You can get more creative with the MDX, there are many ways of achieving this, but this method keeps it simple and will not interfere with any other attribute filters you may have applied.
      Regards
      Alex

  • Hi Alex,

    I fixed the issue above by defining heirarchy for each dimemnsion. Now the YTDs and PY YTD are calculating correctly.

    I have another question. Within the same dimension I have Month Name and Month Number that the end users wants to be able to apply dynamic date calculations to as well. The heirarchy I created to fix my previous issue was

    Date.YR
    Date.QTR-YR
    Date.MTH-YR

    There are also MTH-Name and MTH-NBR in this dimemsion but I am unable to add this to the hierarchy. Each time I do I get an error.

    Is there a way to link the MTH-Nme to the YR with out changing the structure. When I try something like this

    WITH MEMBER [Date Calculation].[Date Calculation].[PY YTD] AS
    AGGREGATE(
    {[Date Calculation].[Date Calculation].[Selected Date]} *
    PERIODSTODATE
    ( [DATE].[MTH-Name].[MTH-Name],

    PARALLELPERIOD
    ([DATE].[MTH-Name].[MTH-Name],
    1, {crossjoin([DATE].[MTH-Name].[MTH-Name],
    [DATE].[Date-Hierarchy].[YR],)})
    ))

    I get #Error .

    What am I doing wrong? Or is this even possible to do? Thank you.

    • Hi Tonia
      I’m not really sure I understand what you’re trying to do – and I really would need to see the cube to understand the details of what you’re trying to do and the problems you’re encountering. Just seeing a small section of MDX in isolation is not enough to understand the problem.

      One problem I do see though is if MTH-Name contains Jan, Feb, Mar, etc., then you can’t do a PeriodsToDate or ParallelPeriod on it, as it’s not a specific date. Jan could be Jan 2014, Jan 2015, etc. So date functions will not work.

      Instead, keep doing the calculations on the MTH-YR attribute, add an attribute relationship between MTH-YR and MTH-NAME, then you can access the month name as a property of the MTH-YR member
      using something like [Date].[MTH-YR].CurrentMember.Properties(“MTH-Name”)

      Regards
      Alex

      • Thank you Alex. I will try to access the month name as a property of the MTH-YR member.

        I will let you know if this works.

  • Hi Alex,

    0

    Sign in to vote

    Dear David,

    In Date hierarchy.

    Sum({Null:[Date].[Year – Month – Date].CURRENTMEMBER},[Measures].[AmountCur])

    When I selected the date from 1st Jan 2013 to 31st Jan 2013. It is taking 31stJan and calculating the Sum from Null to selected date. However, In my case it should take the 1st jan to calculate the Sum from Null to selected date.

    For eg: when I select the date as Jan month or say Jan 5th to Jan 31st

    For now it is taking as Where Date < 31-Jan. But in my case it should take the date as Date < 1-Jan if I selected total jan month

    or Date < 5-Jan , if I select Jan5th – 31Jan .

    How to achieve this?

    Plz Suggest.

    Thanks in advance.

    Regards,
    Rakesh

    • Hi Rakesh
      Not sure who David is – are you on the right blog? Or just copying and posting the same question everywhere?
      Anyway, CURRENTMEMBER is a single member, not a set. So why are you selecting a set of dates in the first place?
      And how are you selecting this set of dates?
      Regards
      Alex

  • Hi Alex
    Can you help? I’m using a PeriodsToDate function to calc MonthToDate of Gl Transactions. I’d like to use multiple calendar hierarchies.

    Using your example above I’ve tried. I always get Value# whenever I query my cube.

    Below is my calculation, can you guide me to the correct way of achieving this? Hopefully I am making a simple mistake with me syntax!

    CREATE MEMBER CURRENTCUBE.[Measures].[Gl Amount MTD]
    AS Aggregate
    (
    PeriodsToDate
    (
    {NULL:[Times].[Natural Calendar CCL].[Calendar Month]
    , [Times].[Natural Calendar CCL].CurrentMember}

    * {NULL:[Times].[Fiscal Calendar CCL].[Fiscal Month]
    , [Times].[Fiscal Calendar CCL].CurrentMember}

    * {NULL:[Times].[Scirt Calendar CCL].[Scirt Calendar Month]
    ,[Times].[Scirt Calendar CCL].CurrentMember}
    )
    ,[Measures].[Gl Amount]
    ),
    FORMAT_STRING = “#,##0.00;(#,##0.00);0;0”,
    NON_EMPTY_BEHAVIOR = { [Gl Amount] },
    VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = ‘Ft Gl Trans’ ;

    Regards
    Read

    • Hi Read
      You’re passing invalid parameters to the PeriodsToDate function. PeriodsToDate requires a level and a member. You’re passing a crossjoined set.
      PeriodsToDate can be replaced with the range operator ‘:’, so you usually don’t need them both.

      You need to do three different PeriodsToDate, each on a different hierarchy. Then crossjoin the results, passing that to the Aggregate function.

      Something like this will hopefully work, although it’s untested!

      CREATE MEMBER CURRENTCUBE.[Measures].[Gl Amount MTD]
      AS Aggregate
      (
      PeriodsToDate
      ([Times].[Natural Calendar CCL].[Calendar Month]
      , [Times].[Natural Calendar CCL].CurrentMember)

      * PeriodsToDate
      ([Times].[Fiscal Calendar CCL].[Fiscal Month]
      , [Times].[Fiscal Calendar CCL].CurrentMember)

      * PeriodsToDate
      ([Times].[Scirt Calendar CCL].[Scirt Calendar Month]
      , [Times].[Scirt Calendar CCL].CurrentMember)

      ,[Measures].[Gl Amount]
      ),
      FORMAT_STRING = “#,##0.00;(#,##0.00);0;0″,
      NON_EMPTY_BEHAVIOR = { [Gl Amount] },
      VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = ‘Ft Gl Trans’ ;

      • Hi Alex
        Thanks for your suggestion. I tried this approach before contacting you, without success. Unfortunately your example returns #Value!.

        I have resulted to scoping and found a solution that works, though is not as flexible as your suggestion. I would really like to try and find a solution along the lines you suggested above. This would allow me to use other calculations within these calcs.

        Can you give me your thoughts on what might be wrong with this approach?

        Regards
        Read

        //Copied from your post
        CREATE MEMBER CURRENTCUBE.[Measures].[Gl Amount MTD]
        AS Aggregate
        (
        PeriodsToDate
        ([Times].[Natural Calendar CCL].[Calendar Month]
        , [Times].[Natural Calendar CCL].CurrentMember)
        * PeriodsToDate
        ([Times].[Fiscal Calendar CCL].[Fiscal Month]
        , [Times].[Fiscal Calendar CCL].CurrentMember)
        * PeriodsToDate
        ([Times].[Scirt Calendar CCL].[Scirt Calendar Month]
        , [Times].[Scirt Calendar CCL].CurrentMember)
        ,[Measures].[Gl Amount]
        ),
        FORMAT_STRING = “#,##0.00;(#,##0.00);0;0″,
        NON_EMPTY_BEHAVIOR = { [Gl Amount] },
        VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = ‘Ft Gl Trans’ ;

  • Hi Alex!

    Thanks for the help!
    Regards,
    Gabor

  • Hello Folks ,
    I connected to cube through excel ,
    I filter pane i selected date , I Select number of dates Let say

    2015-01-01,
    2015-01-05,
    2015-01-06,
    2015-01-07

    Now i want to calculate the number of days between min date and max dates between selected dates in my cube
    Thanks

    • Hi Sanjeewan
      I’m not aware of any way of doing this I’m afraid.
      If you do find away, please do take the time to let me know here, I’d be interested to know.
      Regards
      Alex

      • To do this in Adventure work

        create dynamic set [SelectedDates] as
        existing [Date].[Date].[Date].members;

        create member currentcube.measures.DaysBetweenMinAndMaxDates as
        count(SelectedDates.item(0):tail(SelectedDates).item(0));

  • Hi,

    Considering two role playing dimension [Order Date], [Issue Volume Date] how to return Y-1 value regardless of the dimension selected?
    I tried define calculation in scopes

    ([Order Date].[Year-Month].members,[Measures].[Amount Y-1])=
    (ParallelPeriod([Order Date].[Year-Month].[Calendar Year],1,[Order Date].[Year-Month]), [MEASURES].[Amount]);
    //–iv dimension
    ([Issue Volume Date].[Year-Month].members,[Measures].[Amount Y-1])=
    (ParallelPeriod([Issue Volume Date].[Year-Month].[Calendar Year],1,[Issue Volume Date].[Year-Month]),[MEASURES].[Amount])
    ;

    But it seems that the second overwrite the first.

    Thank you for suggestion.
    Best,
    B.

    • [Order Date].[Year-Month].members and [Issue Volume Date].[Year-Month].members will both include the ‘All’ member. Therefore the second scope will always override the first as there will always be a valid selection in the second scope, either a specific member, or the All member.
      To get around this, replace the members statements with
      [Order Date].[Year-Month].[Year-Month].members
      and
      [Issue Volume Date].[Year-Month].[Year-Month].members
      Hopefully that will fix it.
      Alex

    • Hi,

      Thank you!!! Works perfectly for [Year-Month]

      for [Year] Level I used
      [Issue Volume Date].[Year-Month].[Year].members respectively

      and works like charm too.

      Best,
      B.

  • Hi Alex,

    Thanks a lot for the excellent write up! It was very useful 🙂 I would like to check if it is possible to do the below scenario

    I’ve to show the Opening balance by year for all the months in that year. For eg.
    Year Opening Balance
    201301 $1000
    201302 $1000
    .
    .
    201312 $1000
    For 2014 I’ll receive new balance and would like to show them as Opening balance for all 2014 months
    201401 $1500
    .
    .
    201412 $1500

    Please let me know if you need any additional information.

    Thanks a lot for your help!
    Sankar

    • The best way to approach this would be to navigate around the hierarchy.
      From a month, you can identify the first month in the year by using
      [Date].[Year Month Hierarchy].CurrentMember.Parent.FirstChild
      Which first of all goes up one level to the Year level, and then returns the first child (January).
      Alternatively there’s a shortcut using FirstSibling:
      [Date].[Year Month Hierarchy].CurrentMember.FirstSibling

      Once you have this, just query the opening balance for the member that’s returned:
      ([Measures].[Opening Balance], [Date].[Year Month Hierarchy].CurrentMember.FirstSibling)

      Regards
      Alex

      • Thanks a lot for the quick response!!

        I tried the below but repeats the YTD balance of each month as opening balance.

        With

        MEMBER [Measures].[UTP OB] AS
        ([Measures].[UTP Amount], [Time].[Hierarchy].CurrentMember.FirstSibling)
        Select {
        [Measures].[UTP Amount],
        [Measures].[UTP OB],
        } ON COLUMNS ,
        {[Time].[Year Month].[Year Month] } ON ROWS
        From AITRA

        I used the below before and worked fine for 2014 balances. With 2015 balances in FACT It just repeats 2014 balances.

        AGGREGATE(
        {NULL:[Time].[Year Month].CurrentMember}
        , [Measures].[OB UTP Amount]
        )

        Thanks again!

        • I’m assuming that your calculated measure was actually the following: (typo in your post)
          MEMBER [Measures].[UTP OB] AS
          ([Measures].[UTP Amount], [Time].[Year Month].CurrentMember.FirstSibling)

          If so, this will only work if the Year Month hierarchy has (at least) two levels in it; Year and YearMonth.
          From what you describe, it sounds like it only has a single level (YearMonth).

          If you change this in your dimension design then it should work correctly for all years.

          • The Time hierarchy has Year, Year Month and Date with it. Apologies for not making it clear. I should have explained it better.

            I have 2 Measures UTP Amount and UTP Balance. The second measure is a manually populated in the table once in a year with the closing balance. For instance UTP Balance for 2014 is loaded as closing balance in 201312 period. For all months of 2014, this balance will be displayed. Similarly for 2015, a one time balance is loaded in 201412 under UTP Balance field and is expected to show this balance for entire 2015 without aggregation.

            I’m trying to achieve this with a calculated measure. Hope I’ve explained it better.

            Thanks a lot!

  • Hi Alex,

    I’m new to MDX and I’m in process of automation one report which user generate everyday manually using base cube data. he fetch data in one sheet from cube and map in main sheet as formula.

    The complexity is to create the dynamic calculated measures of month wise transactions columns with putting last year same month value of each device. Measure is available in cube which is transactions count.

    Reports grow horizontally and user wants to compare each device transactions count comparing with previous year months or day with current year month and day.

    Reports consideration period is Jan2014-Dec 2015. current months data shows date wise but once month complete measured rolls up for that month and create new column for completed months and also place last year month columns to see comparison.

    here is the report format.

    Device ID | description | Jan-14 | Jan-15 |1st feb 14 | 1st feb 15| 24feb14|2feb15|feb14 MTD|feb15 MTD..

    Each day, new column will populate for that day for current year as well as for last year, once feb complete, it will populate two columns like Feb-14 | Feb-15. This will go on until Dec 15

    Can i achieve this dynamics field creation through MDX query. any sample mdx query

    Looking forward to hear from you.

    Thanks,
    Niel

    • Hi Niel
      Difficult to explain the process, so better with an example.
      If you run the following query against Adventure Works cube it should give you the results you’re looking for.
      Then it’s just a case of modifying it to work with your cube structure.

      WITH MEMBER [Measures].[Prev Yr] AS
      (PARALLELPERIOD([Date].[Calendar].[Calendar Year], 1, [Date].[Calendar].CurrentMember)
      ,[Measures].[Internet Sales Amount])
      MEMBER [Measures].[Column Name] AS
      [Date].[Calendar].CurrentMember.MEMBER_NAME
      SELECT NON EMPTY DRILLDOWNLEVEL(DESCENDANTS([Date].[Calendar].[Calendar Year].&[2008], [Date].[Calendar].[Month]))
      * {[Measures].[Prev Yr], [Measures].[Internet Sales Amount]}
      ON 0
      ,[Product].[Product].[Product].MEMBERS ON 1
      FROM [Adventure Works]

      Regards
      Alex

      • Thanks for your quick respose but provided query is showing product on row, there is no respective pre yr or internet sales calculation.

        Can you please resend the query.

        Thanks,
        Niel

        • Hi Niel
          The query has products listed on the rows, and the months/days on the columns. Each month/day has two columns, one for the current period, one for the previous year.
          Just run the query in Management Studio against Adventure Works and you’ll see what I mean.
          Regards
          Alex

          • Hi Alex,

            MDX query results work fine as i need to populate such data in Cube so even they can slice and dice without report too.

            Can i convert this query into calculated members in Cube?

            Please let me know the logic.

            Thanks,
            Niel

        • Sure, just add the following into the calculations tab of the cube

          CREATE MEMBER CURRENTCUBE.[Measures].[Previous Year Internet Sales Amount]
          AS (PARALLELPERIOD([Date].[Calendar].[Calendar Year], 1, [Date].[Calendar].CurrentMember)
          ,[Measures].[Internet Sales Amount]);

          • Hi Alex,

            I’ve added calculation but don’t see same previous & current year aggregation month, and current & previous year months day wise transactions.

            I wan to see same results which below query display, so user can simply drag against product.

            Can you please advise how to generate similar columns in Cube.

            WITH MEMBER [Measures].[Prev Yr] AS
            (PARALLELPERIOD([Date].[Calendar].[Calendar Year], 1, [Date].[Calendar].CurrentMember)
            ,[Measures].[Internet Sales Amount])
            MEMBER [Measures].[Column Name] AS
            [Date].[Calendar].CurrentMember.MEMBER_NAME
            SELECT NON EMPTY DRILLDOWNLEVEL(DESCENDANTS([Date].[Calendar].[Calendar Year].&[2008], [Date].[Calendar].[Month]))
            * {[Measures].[Prev Yr], [Measures].[Internet Sales Amount]}
            ON 0
            ,[Product].[Product].[Product].MEMBERS ON 1
            FROM [Adventure Works]

            Thanks,
            Niel

          • Embedding cube calculations into the cube are just that; calculations. You cannot embed a report structure or entire query into the cube.
            You have to write the query in MDX, or drag and drop in Excel etc.
            The embedded measures just provide predefined calculations to simplify the process.

            In Excel, you would have to drag in the relevant measures (‘Internet Sales Amount’ and ‘Previous Year Internet Sales Amount’)
            Then drag in the rows & columns; i.e. the products, and the relevant members of the date dimension.

            You could create a named set within the cube to make the date selection more simple, but you’d still need to manually drag it into the pivot.
            Regards
            Alex

          • Hi Alex,

            Sorry for the trouble again but if you can explain would be great based on your experties in MDX.

            I’ve one more difficulty to use below MDX/even i create calculation in the cube level. This query fetch the last year month total and current year month total so it’s consider complete 30/31 days.

            User has 2 more checks that’s need to be managed while getting each month total and those are below.

            (1) I’ve to exclude if there are any public holiday in previous/current year month(i’ve holiday table).
            (2) If first day of month starts on Sat/Sunday, then sum should be consider from next day. Let’s take example of Feb 2015 & 2014. Feb 1st 2015 came on Sunday, so what user does he go back and look for Feb 2014 1st day, that was fall in Saturday so he will exclude 1st & 2nd feb of last year and start sum of transaction from 3-2-2014 to 28-2-2014
            For current year 2015 will consider same from 3-2-2015 to 28-2-2015
            User based to start date preevious year always.

            Basically, weekdend always going good sale that’s can’t consider if 1st day start on Weekend and secondly, we should avoid considering public holiday too.

            Is this achievable through MDX / cube calculation level? I’m flat out to go either of this option and considering T-SQL stored procedure to intorduce such logic

            Let me know your thoughts.

            Thanks,
            Niel

            WITH MEMBER [Measures].[Prev Yr] AS
            (PARALLELPERIOD([Date].[Calendar].[Calendar Year], 1, [Date].[Calendar].CurrentMember)
            ,[Measures].[Internet Sales Amount])
            MEMBER [Measures].[Column Name] AS
            [Date].[Calendar].CurrentMember.MEMBER_NAME
            SELECT NON EMPTY DRILLDOWNLEVEL(DESCENDANTS([Date].[Calendar].[Calendar Year].&[2008], [Date].[Calendar].[Month]))
            * {[Measures].[Prev Yr], [Measures].[Internet Sales Amount]}
            ON 0
            ,[Product].[Product].[Product].MEMBERS ON 1
            FROM [Adventure Works]

          • You need to add an extra attribute to the Date dimension, populated in the ETL layer or DSV.
            This attribute would indicate whether each date should be included in these calculations, based on the day of week/holiday of the date and the previous years date.
            Then you can simply filter the MDX query by this attribute.
            Regards
            Alex

    • Just noticed an extra requirement that I missed originally – only expand current month, leaving previous months aggregated to the month level.
      The best way to achieve this is to have a new ‘Current Month’ attribute in the date dimension, which you can use to filter in/out the current month, to use different logic for each.
      Then expand my test query, filtering the DRILLDOWNLEVEL results by [Date].[Current Month].[Yes], and then union to it using {,} the set of months for which [Date].[Current Month].[No]

      • Hi Alex,

        Sorry for the trouble again, but I’m completely new for MDX. Hope you understand me as newbie in MDX world.

        Can you please describe the sample query to add ‘Current Month’ and i’ve already “cal_month_no” in my dim_date dimension. Also, if i want we can combine all static attributes(like name, contract expiry year, annualised revenue,zero months in last 12, consecutive zero months, relationship manager name, total txn count week end etc) with cross join in this query, right?

        Here is my dim_date table, if i take exmaple of todays date which is pre-populated
        cal_day_in_week_no
        5
        dim_date_key
        20150226
        calendar_date
        2015-02-26 00:00:00.000
        cal_day_in_week
        Thu
        cal_day_in_month
        26
        cal_month_no
        2

        Thanks in advance and looking forward to hearing from you.

        Cheers,
        Niel

        • The easiest way is to go into your data source view, right click on the date table, and ‘Create Named Calculation’.
          Call this something like ‘Is Current Month’, with an expression of:
          CASE WHEN [Year]=YEAR(GETDATE()) AND [Month]=MONTH(GETDATE()) THEN ‘Yes’ ELSE ‘No’ END
          (replacing [Year] and [Month] with the year and month from your date dimension)

          Then go to the Date dimension, and add this new field in as an attribute, which can then be used in your queries.

          To add extra attributes into the report, just cross join them with the set of products in my original query. Don’t forget to include a NON EMPTY clause though to remove invalid combinations.
          You can change the rows to show anything you like, the query will still work.

          Regards
          Alex

          • Thanks a lot Alex, I’m really appreciate your effort to get this solution.

            I’ve executed provided query and its fetching exactly month/day for Prv yr and current year.
            I’ve also created current month attribute in date dimension to filter in/out current month and after adding attribute, I have explored dimension data which perfectly set yes for current month and rest are NO.

            **Current month attribute: [Date].[Ls Current Month].[Yes]

            Here few queries if you can clarified would be great:

            1) Query results showing as expected but even for Prev Yr month/day, year populate as 2008 but I need it as 2007(or one year less that provided member value)

            2) As you said to expand your test query, filtering the DRILLDOWNLEVEL results by [Date].[Current Month].[Yes], and then union to it using {,} the set of months for which [Date].[Current Month].[No]

            Do I need to replace below section or top of existing query should I use filter?
            “SELECT NON EMPTY DRILLDOWNLEVEL(DESCENDANTS([Date].[Calendar].[Calendar Year].&[2008]”

            if possible can you please let me know how to use this filter and union?

            3) Is that possible to populate below 2more columns in same query, this need to compare with last year. If I am preparing report today 26th Feb then MTD will run for period(1st feb-25feb)
            (1) MTD for getdate()-1(because report run for last processing day)
            (2) MTD for same day last year

            Looking forward to her from you.

            Thanks,
            Nilesh

          • Hello Alex,

            I’ve requirement where my measure comes from named query and those measure column name changes every day/months(some of remain constant, others add new), so if I create measure group based on this named query table.

            Does it create automatic every day while processing cube? I tried it but if underlying query populate more measure column and change existing measure name then processing was giving error.

            I think, if I want to achieve this I have to drop and re-create all measure of this group dynamically through XMLA scripts? even based on this measures, I’ve to add some calculation too but I am not sure how to do this.

            Can you please let me know the best way to achieve this results. I may call this script in my daily ETL load job before cube start full processing.

            Appreciate, if you can give a me small example with script based on adventureworks cube.

            Looking forward to hear from you.

            Thanks,
            Niel

          • I would seriously question the approach of changing measure names on a daily basis – I don’t know the details or justification, but it sounds like a very problematic approach. I’d recommend revisiting this and finding a less volatile solution to the problem…

          • Thank you Alex on responding my all queries so promptly.

            Appreciate your all time to advise such great solution.

            Is there systematic steps by step way to learn/improve MDX knowledge? I’m completely new to MDX and its been good learning from your blog so far.

            Let me know if any books or best links I should follow.

            Regards,
            Niel

          • It’s not a simple query language to learn, it takes time and patience. However I’d heavily recommend the following:
            – MDX courses by technitrain.com
            – ‘Fast Track to MDX’ book. It’s old and expensive, but very good
            – and of course blogs; mine, Chris Webb, Alberto Ferrari, Marco Russo, etc.

            But the main thing is trial and error – start out simple, and progress. If your MDX is to complex, remodel your data to simplify the cube.

  • Hi Alex,
    I have tried numerous approaches, reached the point past frustration and this is the calm before I pick up the PC and throw it out the window… this is my most recent version, please help for the PC’s sake 🙂

    I need to define the income received in the first year on book, then the income in the second year on book,… lastly and the total income received from 4 years onwards.

    Ideally, instead of defining income within the time frames as above, I could have a dimension which can be applied to the various measures but this approach is further than what I currently have.

    //////////////////////////////////////////////////////
    With
    Member [Measures].[Income – Year 0] As
    ([Measures].[Income]
    , LinkMember( [Sales Period].[Sales Year]
    , [Book Period].[Book Year]
    )
    )
    Member [Measures].[Income – Year 1] As
    ([Measures].[Income]
    , LinkMember( ParallelPeriod([Sales Period].[Sales Year].[Sales Year]
    , -1
    , [Sales Period].[Sales Year]
    )
    , [Book Period].[Book Year].[Book Year]
    )
    )
    Member [Measures].[Income – Year 2] As
    ([Measures].[Income]
    , LinkMember( ParallelPeriod([Sales Period].[Sales Year].[Sales Year]
    , -2
    , [Sales Period].[Sales Year]
    )
    , [Book Period].[Book Year].[Book Year]
    )
    )
    Member [Measures].[Income – Year 3] As
    ([Measures].[Income]
    , LinkMember( ParallelPeriod([Sales Period].[Sales Year].[Sales Year]
    , -3
    , [Sales Period].[Sales Year]
    )
    , [Book Period].[Book Year].[Book Year]
    )
    )
    Member [Measures].[Income – Year 4] As
    ([Measures].[Income]
    , {LinkMember( ParallelPeriod([Sales Period].[Sales Year].[Sales Year]
    , -4
    , [Sales Period].[Sales Year]
    )
    , [Book Period].[Book Year].[Book Year]
    )
    : Null}
    )
    Select
    AddCalculatedMembers(Measures.Members) on Columns
    , non empty [Sales Period].[Sales Year].[Sales Year] on Rows
    From [My Cube]
    ;
    //////////////////////////////////////////////////////

    The result is correct for [Measures].[Income – Year 0] however I get a #Error for the remaining calculated measures.

    Kindly kindly help!

    • Hi Alex… any insight regarding previous post?

    • Try this…

      //////////////////////////////////////////////////////
      With
      Member [Measures].[Income – Year 0] As
      ([Measures].[Income]
      , LinkMember( [Sales Period].[Sales Year].CurrentMember
      , [Book Period].[Book Year]
      )
      )
      Member [Measures].[Income – Year 1] As
      ([Measures].[Income]
      , LinkMember( [Sales Period].[Sales Year].CurrentMember
      , [Book Period].[Book Year]).LAG(1)
      )
      Member [Measures].[Income – Year 2] As
      ([Measures].[Income]
      , LinkMember( [Sales Period].[Sales Year].CurrentMember
      , [Book Period].[Book Year]).LAG(2)
      )
      Member [Measures].[Income – Year 3] As
      ([Measures].[Income]
      , LinkMember( [Sales Period].[Sales Year].CurrentMember
      , [Book Period].[Book Year]).LAG(3)
      )
      Member [Measures].[Income – Year 4] As
      AGGREGATE(
      { LinkMember( [Sales Period].[Sales Year].CurrentMember
      , [Book Period].[Book Year]).LAG(4):null}
      ,[Measures].[Income])
      Select
      AddCalculatedMembers(Measures.Members) on Columns
      , non empty [Sales Period].[Sales Year].[Sales Year].MEMBERS on Rows
      From [My Cube]
      ;
      //////////////////////////////////////////////////////

  • Hi Alex,

    Can you please correct my below MDX FILTER function using query, it worked fine without FILTER. As per your reply on 3rd March i’ve populated an extra attribute to my settlement Date dimension which indicate what date needs to be consider for calulcation(there is flag against each date ‘y/n’). I apply that filter in my MDX but its only shows [Terminal].[Agreement Identifier].

    Also, I want to use another filter from same settlement date dimension where ‘ls current month’ Named Calculation created with an expression of:
    CASE WHEN [Year]=YEAR(GETDATE()) AND [Month]=MONTH(GETDATE()) THEN ‘Yes’ ELSE ‘No’ END

    (this is your original advise: Just noticed an extra requirement that I missed originally – only expand current month, leaving previous months aggregated to the month level.
    The best way to achieve this is to have a new ‘Current Month’ attribute in the date dimension, which you can use to filter in/out the current month, to use different logic for each.
    Then expand my test query, filtering the DRILLDOWNLEVEL results by [Date].[Current Month].[Yes], and then union to it using {,} the set of months for which [Date].[Current Month].[No]
    )

    MDX Query :
    ————–
    WITH MEMBER [Measures].[Prev Yr Txn]
    AS
    (PARALLELPERIOD([Settlement Date].[Calendar].[Settlement Calendar Year], 1, [Settlement Date].[Calendar].CurrentMember)
    ,[Measures].[Transaction Count])
    MEMBER [Measures].[Column Name] AS
    [Settlement Date].[Calendar].CurrentMember.MEMBER_NAME
    SELECT
    NON EMPTY
    DRILLDOWNLEVEL(
    FILTER(
    DESCENDANTS([Settlement Date].[Calendar].[Settlement Calendar Year].&[2013],
    [Settlement Date].[Calendar].[Settlement Calendar Month]),
    [Settlement Date].[Settlement Tpdd Cal Flag].&[Y])
    * {[Measures].[Prev Yr Txn], [Measures].[Transaction Count]}) ON 0,
    [Terminal].[Agreement Identifier].MEMBERS ON 1
    FROM [cube_transaction_hourly];

    Looking forward to receive expert solution from you to resolve my issue.

    Thanks,
    Niel

    • Really difficult to debug MDX when you can’t run or test it, but try this…

      WITH MEMBER [Measures].[Prev Yr Txn]
      AS
      (PARALLELPERIOD([Settlement Date].[Calendar].[Settlement Calendar Year], 1, [Settlement Date].[Calendar].CurrentMember)
      ,[Measures].[Transaction Count])
      MEMBER [Measures].[Column Name] AS
      [Settlement Date].[Calendar].CurrentMember.MEMBER_NAME
      SELECT
      NON EMPTY
      DRILLDOWNLEVEL(
      FILTER(
      DESCENDANTS([Settlement Date].[Calendar].[Settlement Calendar Year].&[2013],
      [Settlement Date].[Calendar].[Settlement Calendar Month]),
      [Settlement Date].[Settlement Tpdd Cal Flag].&[Y]))
      * {[Measures].[Prev Yr Txn], [Measures].[Transaction Count]} ON 0,
      [Terminal].[Agreement Identifier].MEMBERS ON 1
      FROM [cube_transaction_hourly];

      • Thanks Alex, it perfectly worked even i tried with WHERE clause and both results are same.

        …..WHERE([Settlement Date].[Settlement Tpdd Cal Flag].&[Y])

  • when I connect to my cube , I select a field in a dimension and apply filter in excel pivot table, it does not display row labels. It will only display row labels if I select a measure. How do I make it apply the filter and just display the row label name. I will appreciate any advice.

    • Hi Tonia
      You just need to right click on the pivot table, select ‘Pivot Table Options’, then the ‘Display’ tab.
      Select ‘Show items with no data on rows’, and the members should be displayed.
      The pivot table essentially does it’s own NON EMPTY functionality. This setting overrides it.
      Regards
      Alex

  • Hi Alex,

    I would like to build following Calculated members, Can you please give sample MDX to get this reullts if you can?

    It wuld okay, if you can give example based on AdventureWorks database, internet sales amt for products etc.

    1. Total Transactions count Weekend Sunday (i’ve date dimesion which holds day_in_week=’Sun’ for respetive dates)
    2. YTD Transactions = Transaction count year to date
    3. YTD Days = Count of days within calendar year to date
    4. Zero months in last 12 = Number of zero transacting months in the last 12 months rolling window
    5. Consecutive zero months = Number of consecutive zero transacting months in last 12 months rolling window

    Looking forward to hear from you some good advise.

    Thanks,
    Niel

    • Hi Niel
      This blog is to provide help when people get stuck on a problem, unfortunately I’m unable to do development work for you via this blog, or offer continuous help on every problem you encounter. If you need this level of help then I’d heavily recommend engaging the services of a good BI consultant who can provide you with dedicated one to one training and advice. This is due to the fact that fixing an MDX problem well requires a deeper understanding of data modelling and an understanding of the whole cube, which is almost impossible via a blog post. Therefore there’s a very good chance that I may be giving you sub-optimal advice, leading to further problems down the line.
      I hope this makes sense, and good luck with the project.
      Regards
      Alex

  • Hello Alex,

    I’ve built below query to get Annualised Revenue(SUM of revenue for last 12 complete months) for each terminal device but its showing only terminal names, Annualised Revenue shows (null).

    Any correction needed for this MDX Query? bascially i wwant to convert it as Calculated member

    Here is ther query:

    WITH MEMBER [Measures].[Annualised Revenue]
    AS SUM(ClosingPeriod([Invoice Date].[Calendar Month].[Invoice Calendar Month],
    [Invoice Date].[Calendar Month].[All Periods]).Lag(12)
    : ClosingPeriod([Invoice Date].[Calendar Month].[Invoice Calendar Month],
    [Invoice Date].[Calendar Month].[All Periods]),
    [Measures].[Amount])
    SELECT [Measures].[Annualised Revenue] ON 0,
    [Terminal].[Terminal ID].MEMBERS on 1
    FROM [cube_txn]

    Do i need to add where cluase as “where ( [Invoice Date].[Calendar Month].[Invoice Calendar Year].&[2013])”

    Your help will be much appreciated.

    Thanks,
    Niel

  • Hi Alex,

    I am new to MDX, but I have written the following statement which achieves what I want it to, namely it checks that the opening value of a balance sheet account is or is not zero and if it is it calculates future balances based on predicted cash receipts and payments. However, it could be better. For example, I don’t particularly want to show historical balances. So, is there a way of working out which period I am in today, in order to be able ‘know’ what is in the future and what is in the past?

    Thanks Mark

    SCOPE ([Time].[Time].[Time_L5].Members);

    [Account].[Account].[Account_L4].&[829] = IIF([Account].[Account].[Account_L4].&[829] = 0,
    (PARALLELPERIOD([Time].[Time].CurrentMember.Level, 1, [Time].[Time].CurrentMember), [Account].[Account].[PEL-CASH]) +
    (PARALLELPERIOD([Time].[Time].CurrentMember.Level, 1, [Time].[Time].CurrentMember), [Account].[Account].[PEL-REC]) +
    (PARALLELPERIOD([Time].[Time].CurrentMember.Level, 1, [Time].[Time].CurrentMember), [Account].[Account].[PEL-PAY]) +
    (PARALLELPERIOD([Time].[Time].CurrentMember.Level, 1, [Time].[Time].CurrentMember), [Account].[Account].[PEL-JC]) +
    (PARALLELPERIOD([Time].[Time].CurrentMember.Level, 1, [Time].[Time].CurrentMember), [Account].[Account].[PEL-BC]),
    [Account].[Account].[Account_L4].&[829]);

    END sCOPE;

    • Hi Mark

      My preferred option is to add a new attribute into the date dimension. Either in the DSV or (preferred) in the source view for the date dimension. Something like:
      CASE WHEN [Date]>GetDate() THEN ‘Future’ ELSE ‘Past’ END AS DateFuturePast

      Then so long as you process the date dimension after midnight every day, it will always give you a really easy way of filtering dates without having to bother with complex MDX or dynamic sets etc.

      Regards
      Alex

  • Wow! Thanks for the swift reply. Forgive me, but what is a DSV? And by source view do you mean do this at the ETL stage?
    One more question, I know I can write that case statement in T-SQL but is it valid in MDX as well?

    Thanks Mark

    • The DSV is the Data Source View – which you’ll find in your Solution Explorer window.
      You could write the expression as MDX, but then it would have to evaluate at runtime for every query, and you couldn’t make use of the attribute in aggregations etc.
      So for this case I would write it in SQL so it’s calculated at processing time. The DSV is simply a query which runs against the data warehouse at processing time, and is used to populate the dimension or measure group.

      Adding the calculation to the DSV would effectively turn the cube source query into something like this:
      SELECT *
      , CASE WHEN [Date]>GetDate() THEN ‘Future’ ELSE ‘Past’ END AS DateFuturePast
      FROM dim.Date

      Yes you could calculate it in the ETL process instead, but I don’t see the benefit as it would force you to run the ETL every night when you may not want to. Something as simple as this can just be done in the DSV. Then it doesn’t matter whether the ETL has run or not, the cube will always be correct (so long as it has been processed).

      Regards
      Alex

  • Fantastic, thank you very much. Are there any plans for SQL Relay sessions this year (I have just realised I missed the SQL Bits earlier this month).

    Mark

    • Absolutely, we’re planning 8 events around the UK from 5th to 15th October. No further details yet I’m afraid, but watch this space!
      The SQL Bits videos should also be coming on-line shortly, there were some great sessions so keep an eye out on the Bits website.

  • Hi Alex,

    Sorry to disturb you again, but it turns out that the calculation I described above is crushing the performance of the cube (when viewed in excel, but not when published as a web page). Could I move this to the DSV as well?
    Or, can you think of anything that I can do to try to optimise the calculation?

    Regards Mark

    • Hi Mark
      You’d struggle to do it in the DSV as you use Time.CurrentMember, so the MDX is aware of which time hierarchy level is being looked at. You don’t have that context awareness in the DSV.
      Regarding writing more efficient MDX, with something like this it would be necessary to have a copy of the processed cube in order to understand the situation in much more detail – so very difficult do do via a blog post I’m afraid!
      Regards
      Alex

  • Hi Alex,

    I am new to MDX and tried various MDX query to handle this requirement but couldnt get output as expected. Below is my table structure.

    Policyno Startdate Enddate
    56486 20150110 20150510
    78945 20141219 20150224

    I want to calculate policy backlog’s between startdate and end date .Below is the expected output

    YearWise blog

    2014 1(78945)
    2015 0

    MonthWise blog

    2014 Dec 1 (78945)
    2015 Jan 2 (56486,78945)
    2015 Feb 1
    2015 mar 1
    2015 apr 1

    I want to calculate similar backlog’s based on week, day wise. Do i need to create four separate measures for day, week,month,year wise or single calculated measure is enough to handle this requirement.
    Could you please help me to write the calculated member for this or suggest on possible solution to achieve this.
    Your help will be much appreciated.

    Thanks,
    Murugan

  • Hi Alex,

    Can you suggest me:

    1. I have two different date hierarchies.
    2. Both have different attribute relationships.

    I need to display Last Non Empty Value of a member. I cannot use Last Non Empty because my measure column is Distinct Count.

    I have a added a calculation as follows, which works for one hierarchy, I need to modify this to support other hierarchy which is [Date].[Fiscal Year Month].

    sum(
    generate(
    Descendants([Date].[Fiscal Date].currentmember, [Date].[Fiscal Date])
    ,tail(
    nonempty(Existing [Date].[Fiscal Date].[Date Desc], [Measures].[A])
    ,1)
    )
    ,[Measures].[A])

  • Hi Alex,

    First, thank you, thank you, thank you for this blog and thread!

    I’ve been pulling my hair out trying to get a Multi-Date Dim Calculation to work out.
    I need a little help. I can’t get the most basic proof of concept working of what you have above.
    Very frustrating, since I am now so close thanks to your great thread.

    The MDX snippet you give me gets me the dates,
    but w/ “#error” for the calculation results.

    For instance:

    CREATE MEMBER [MyCube].[Measures].[MyFoo] AS
    AGGREGATE(
    { [Service Start Date].[by Month Hierarchy].[Month].CurrentMember }
    * { [Service End Date].[by Month Hierarchy].[Month].CurrentMember }
    ,
    [Measures].[MyValue]
    ) ;

    SELECT
    [MyFoo] ON 0,
    [Service Start Date].[by Month Hierarchy].members ON 1
    FROM [MyCube]

    Is there some kind of dim design property
    I should check that could be causing my #error (other than defaulting to All)?

    My ultimate goal is:
    -A query solution for what really should of been set up at a lower design level.
    -A financial ratio where:
    -The numerator is the measure over a last rolling 12 months of the a date dim.
    -The denominator is the measure over a previous 12 rolling months of a different date dim.
    I can get either numerator or denominator working in MDX, but not both in the same query.

    • Hi Doug
      I’m not sure I understand the problem – Why do you need to use the AGGREGATE(xxx * xxx) in the first place? In my example it’s necessary as we want to create a set from null:CurrentMember from multiple hierarchies. However if you’re not doing that, and are just filtering by one hierarchy member or another then can’t you just use the [Measures].[MyValue] measure directly without a calculation? Maybe I’m misunderstanding the problem…

      Either way, if you can get calculations for the numerator and denominator working separately then just create two calculated measures, one for each, then a third to do the calculation of one divided by the other. Then just hide the first two.
      Regards
      Alex

      • Hi Alex,

        Thank you so much for your reply and insights..
        Wish I could buy you a pint.

        My bad. I had muddied the waters with two distinct issues.
        Issue #1 is not getting a basic example to work from above (as my mini Proof of Concept).
        Issue #2 is the broader problem, I am trying to solve.
        I had figured if I could get #1 working, then #2 would flow naturally.

        For #1) I just worked off the wrong snippet. I had been working with the MDX snippet w/ and without the “null:” component in the set declarations.. In both bases, it just gives me an #Error for the calculation. I should of included the snipped above w/ Null: to avoid any confusions. My apologies. I figured I was missing some else, probably obvious, instead.

        For #2) The bigger issue. I was hoping to spare you this logic ugliness. Essentially your approach of splitting this up into multiple calculated measure IS the current approach. Four measures in fact. The problem is it’s not completely working for me, so I am trying to get simpler examples working first and build up from there.

        I have a current solution that looks like this:

        RootCalcMember1: Measure Count over [Product Dim] – Count of Product A
        RootCalcMember2: Measure Count over [Product Dim] – Count of Product B

        TimeCalcMember1: Sum of RootCalcMember1
        over: [Start Date] ParallelPeriod (12 months ago)
        to: [Start Date] ParallelPeriod (Current month)

        TimeCalcMember2: Sum of RootCalcMember2
        over: [Start Date] ParallelPeriod (13 months ago)
        to: [Start Date] ParallelPeriod (24 months ago)

        RatioCalcMeasure: TimeCalcMember1/ TimeCalcMember2

        The above works and predates my arrival.

        My task is to try and create an alt version that will
        now span two different date dimensions:

        RootCalcMember1: Measure Count over [Product Dim] – Count of Product A
        RootCalcMember2: Measure Count over [Product Dim] – Count of Product B

        TimeCalcMember1: Sum of RootCalcMember1
        over: [START Date] ParallePeriod (12 months ago)
        to: [START Date] ParallePeriod (Current month)

        TimeCalcMember2: Sum of RootCalcMember2
        over: [END Date] ParallelPeriod (13 months ago)
        to: [END Date] ParallePeriod (24 months ago)

        RatioCalcMeasure: TimeCalcMember1/ TimeCalcMember2

        In trying various iterations of this logic I find I can get a result when I only include one date dimension in the layers of calculated members. It’s why the basic idea of aggregating the date dimensions first, expressed in this thread seems like the way to go. However, if I can’t get the foundation level working, I am a bit in trouble? 😉

        Thanks…

        • Ok, Well, I just got the my #1 basic example to work, by playing with syntax.
          Doh! Blame it on re-learning MDX syntax again after almost 10 years away from it.

        • And on last little bit of info:

          — This now works for me as Basic PoC (Works)–
          WITH MEMBER [Measures].[Test]
          AS
          AGGREGATE(
          { Null:[Service Date].[by Month Hierarchy].CurrentMember }
          * { Null:[Service End Date].[by Month Hierarchy].CurrentMember }
          ,
          [Measures].[Count]
          )

          SELECT [Test] oN COLUMNS ,
          [Service Date].[by Month].[Month] ON ROWS
          FROM [MyCube]

          — But adding an extra complication makes it all fall apart with an #error–
          WITH
          MEMBER RootMeasuredCalc AS
          SUM(
          [Measures].[Count],
          {[Product].[IsProductType].&[Renewal]}
          )

          MEMBER [Measures].[Test]
          AS
          AGGREGATE(
          { Null:[Service Date].[by Month Hierarchy].CurrentMember }
          * { Null:[Service End Date].[by Month Hierarchy].CurrentMember }
          ,
          RootMeasuredCalc
          )

          SELECT
          [Test] ON COLUMNS ,
          [Service Date].[by Month].[Month] ON ROWS

          FROM [MyCube]

          • Hi Doug

            I think this is the wrong approach for what you’re looking to achieve.
            This method of cross joining two date hierarchies is for applications where a single calculation needs to honour either one date hierarchy selection, or another, but both applying to the same calculation.

            In your example, you want two different calculations to each use a different date hierarchy. So there’s no benefit or point in cross joining the two dimensions. They need to be kept separate.

            What you actually need to do is:
            – Start out with the principle that it is [Service Date] that is acting as the definition of what is the ‘current date’, this is the dimension on the rows, and essentially is the starting point for each calculation.
            – TimeCalcMember1 then wants to find data, where [Start Date] is within 12 months of the relevant [Service Date]
            – TimeCalcMember2 then wants to find data, where [End Date] is within 24-12 months of the relevant [Service Date]

            So the question becomes, how to we take the current member of the [Service Date] dimension and apply it to the [Start Date] or [End Date] dimensions.
            You do this by using the LINKMEMBER function.
            http://mdxpert.com/Functions/MDXFunction.aspx?f=33

            Something like this (freehand MDX, so please excuse any errors!):

            MEMBER [Measures].[TimeCalcMember1]
            AS
            SUM({PARALLELPERIOD([Service Start Date].[by Month Hierarchy].[Year], 1, LINKMEMBER([Service Date].[by Month Hierarchy].CURRENTMEMBER, [Service Start Date].[by Month Hierarchy])) : LINKMEMBER([Service Date].[by Month Hierarchy].CURRENTMEMBER, [Service Start Date].[by Month Hierarchy])}
            ,
            RootCalcMember1
            )

            Hope this helps
            Alex

          • Hi Alex, thank you many times over for your reply.

            But sadly, I am not sure this suggestion will work.
            I may be totally missing something, so beg your forgiveness, if I don’t see it.

            I don’t have one service date that needs to be attached to either a start or end date dimension.
            The fact record contains both a start and an end date.Behind the scenes there is a separate dimension for each of the keys, but the same underlying date (a role playing dim?).. I don’t really have a single central key to link them both up that I can define a date range against.

          • In which case, how are you defining ‘Today’ from a report perspective? If you need to go back 12 and 24 months, you need to be able to provide the user with a date selection that defines the report date, from which other date based calculations can then work from.

            You can’t let the user filter both the Start and End date hierarchies, as they will interfere with each other. So I’d suggest that you need to add a common Date dimension for this purpose. It doesn’t have to be related to the fact table, it’s just used to provide a reporting date selection.

  • Hi Alex, Thank you again!

    I think you might be on to something, what I need to try and work out is if the start date is also the proverbial “today date” for my purposes..

    To answer you question (and this starts to get even deeper into the business need I was trying to spare you from). Suffice to say, there really is no independent “today” concept on the fact now. There is a Service Start & a Service End to a particular fact. Really, the only relationship, is that the end is further on than the start. Now, in the past, there was a “today” ..it was the start date and they just dynamically determined the end date based on a number of other factors, but that won’t meet needs any longer, hence the adding of stored end date (which can be any number of days out) , and also the need to add a new financial reporting ratio to go with it. The interface is an open ended pivot where it is known only certain combinations will produce a result.

    I was imagining “today” as relative based on weather your looking at the start…or looking at the end date. In each case, it’s looking back over previous month ranges of that particular date to get their mutually independent numerators and dominator values. This was all fine and dandy until multiple date dimensions got involved and had to be honored.

    I only have a couple of more rounds left in me, before I toss in the white towel and declare this not possible and try to run two distinct mdx queries in Excel and combine the results there…

    • I just wanted to close this out by saying I think I did find a solution that will work for the moment.
      I ended up constructing a semi-additive separate measure group and based my calculated members off of that.

      The fact table for the new measure group is a union of two data sets out of the original fact table. One for the numerator member and one for the denominator member. Each of the these two members is in their own field and is a distinct measure so when rolling up in the cube they don’t interfere with each other. To get around the date combination technical issue, they both use a single date dimension. In the case of the numerator the single date dimension is based on the original Fact’s start date, but aliased. In the case of the denominator member it’s based off the original end date but aliased as the same name as the start date.

      So far my calculations are working and tie-ing out.
      It’s a hack, but good enough for the moment.

  • Dear Alex,
    How are you? I hope fine! 🙂
    I need you help… can I?

    I’ve followed you suggestion some time ago, but now I need to make some improvements on performance…
    The problem is when I use a Document Dimension that has 400.000 records…

    The MDX try to create the stock level for each document even if no data is present for the document.
    DO you know how can I avoid this?
    Thank you !!

    • Hey Pedro
      It sounds like you’re using MDX to solve a problem that may be better being solved in the data modelling/ETL layer. I’d re-assess what you’re trying to calculate, and determine what the optimal data model should be.
      Have a look at the following video for tips on how to re-think your cube data model
      http://sqlbits.com/Sessions/Event11/Data_Modeling_for_Analysis_Services_Cubes
      Regards
      Alex

  • I started to tried to add to ETL the stock snapshot… the amount of records pass from 2 million (just movements) to 200 milions…
    The solution are in production working good! Just a problem, when add a Document Dimension…
    Cheers and thanks!

  • Hi Alex,

    You have a great blog. It is the very first time I am writing a question in anyone’s blog. My question is also related to SSAS and MDX query. Since Cubes keep the history our client is looking for a count of measures and keys in a specific time. E.g. They would not only like to the the count of different account statuses as of today but what was the count on March 2nd, or what was the count between year 2012 and 2013. I am not sure how to achieve this. Do I use “tail” or how can I create a calculation or a member that anytime it will be dragged and dropped by customers and whatever month, day, year they choose it will show the count for that period of time.

    I appreciate your time and effort to look into this for me.

    Thanks a lot in advance and hope to hear from you soon.
    Best Regards,
    Rim

    • Hi Rim

      To display a count of measures you need to add a new measure to the measure group, with the aggregate function of “Count”. This will count the number of records in the underlying fact table. This will automatically work with any date range selected. The date selection filters the measure group, and so the record count measure decreases. If you want more control, then you can add a new column to the underlying fact table/view, with a fixed value of 1. Then you can just “sum” this new field. The result will be the same, however you can then add logic into the source table/view to only have a value of 1 if the record meets certain criteria (e.g. Value>0 or something).

      Regards
      Alex

  • Hi Alex,

    I am stuck in a situation where I want to use YTD for three different calendars of our company and don’t want to create three different YTD calculations. However I want to make this work for any measure not for a particular measure

    If I create one YTD and try to use in context of three calendars in SCOPE statements then it doesnot give my right results. Following is my syntax but It does not work. Please advise if it’s possible or not.

    SCOPE([Billing Date].[SalesCalendar].MEMBERS);

    ( [Aggregate].[AGGREGATE CODE].[YTD] )
    = Aggregate({[Aggregate].[AGGREGATE CODE].DefaultMember}*{PERIODSTODATE([Billing Date].[SalesCalendar].[Sales Calendar Year],[Billing Date].[SalesCalendar].CURRENTMEMBER) } );

    END SCOPE;

    SCOPE([Billing Date].[FinancialCalendar].MEMBERS);

    ( [Aggregate].[AGGREGATE CODE].[YTD] )
    = Aggregate({[Aggregate].[AGGREGATE CODE].DefaultMember}*{PERIODSTODATE([Billing Date].[FinancialCalendar].[Financial Year],[Billing Date].[FinancialCalendar].CURRENTMEMBER) } );

    END SCOPE;

    However if I comment on one of SCOPE statement , other one works but both don’t work simultaneously in context of different calendars.

    Any gudiance will be helpful.

    Regards,

  • I want to calculate closing inventory and I have 1 date dimension having calendar and fiscal dates

    I have 4 hierarchies in my date dimension:

    Fiscal (which is Fiscal Year / Quarter / Period and Date)
    Fiscal – Period (Fiscal Year / Period)
    Calendar (Year / Quarter / Month / Date)
    Calendar Year – Month (Year / Month)

    This work fine if I want to do something like this:

    Fiscal Year – Period Sales
    2014 500,000
    P1 200,000
    P2 100,000
    P3 200,000

    2015 700,000
    P1 300,000
    P2 200,000
    P3 200,000

    But I want to display Fiscal Year on rows and Periods on columns, so I also made available Fiscal Year and Period so I can have this:

    Fiscal Year P1 P2 P3 Total
    2014 200,000 100,000 200,000 500,000
    2015 300,000 200,000 200,000 700,000

    This works fine when using values from the cube.

    But I wanted to calculate Closing Inventory, so here is the code:

    AGGREGATE(
    {null:[Date].[Calendar].CurrentMember}
    * {null:[Date].[Calendar Year – Month].CurrentMember}
    * {null:[Date].[Fiscal].CurrentMember}
    * {null:[Date].[Fiscal Year – Period].CurrentMember}
    * {null:[Date].[Date].CurrentMember}
    , [Measures].[Unit])

    For my hierarchies (Calendar, Calendar Year – Month, Fiscal, Fiscal Year – Period) it works perfectly.

    But since I wanted to have fiscal year on rows and periods on columns, I added this to the code:

    AGGREGATE(
    {null:[Date].[Calendar].CurrentMember}
    * {null:[Date].[Calendar Year – Month].CurrentMember}
    * {null:[Date].[Fiscal].CurrentMember}
    * {null:[Date].[Fiscal Year – Period].CurrentMember}
    * {null:[Date].[Date].CurrentMember}

    * {null:[Date].[Fiscal Year].CurrentMember}
    * {null:[Date].[Period].CurrentMember}

    , [Measures].[Unit])

    After this change, hierarchies were still giving the same number but when using Fiscal Year (rows) and Period (columns) I was getting different numbers.

    How can I have this closing inventory calculation work ?

    BTW, in the hierarchy called “Fiscal Year – Period”, the key for the Period is a combination of [Fiscal Year] and [Period], making it unique but I can’t use this same key when setting up [Period] because when I put [Period] on columns, it displays periods separately among fiscal years:

    P1 P2 P3 P1 P2 P3
    2014 200,000 100,000 200,000
    2015 300,000 200,000 200,000

    thank you

    • I found the cause of my problems and it works now !

      My hierarchy [Fiscal Year – Period] was using [Period Full]. It’s key was [Fiscal Year + Period] but in fact it has to be [Period].

  • I need one MDX script
    Below is the relational query
    Select COUNT(distinct empID) from Emp
    Where EmpType >10

    I have Employee Dimension with attribute empID ,EmpType
    I created distinct count (Employee.EmpID.EmpID) as Measure

    How can i get the distinct empID count using EmpType in MDX

    • It should be as simple as:

      SELECT [Measures].[EmpID Distinct Count] ON 0
      FROM (SELECT {[Employee].[EmpType].[11]:null} ON 0 FROM [Cube Name])

      Alex

  • Hi Alex,

    I was making new enhancements to SSAS cube and deployed changes on UAT and cube was working properly. After few months, user sent UAT feedback with few issues but when re-processed the cube without making any changes at all in assumption that issues would be fixed.

    In the cube designer when I check ‘Browser’ tab and drag measures, no data is seen. i can find new expected data in dimensions and no data in measures. It is basically empty.

    I would be okay if data is not there for expected month/date data as per my changes but old existing data(ex,2011-2014) should be impacted but nothing is appearing. if i click on DSV or cube structure data explore shows data.

    I’ve re-process cube many times, imported latest production cube in BIDS2008 and deploy, processed but finally when cube process complete i try to browse and just dragged amount measure, its EMPTY.

    One thing i noticed, this is happened to 2 measure group which have parition, rest all measure group are okay.

    Would you please shade some lights on how to fix this problem.

    Looking forward to get some quick resolution tips.

    Thanks,
    Niel

    • one more things Alex..

      This cube has been used Unary Operators in Parent-Child Dimensions / roll up.

      if i get production copy, it should work without any issue in development box but after processing measure are empty

      Thanks,
      Nilesh

    • Hi Niel
      It sounds like the source queries for the partitions are incorrect, and therefore not bringing in the data.
      Look at the properties of one of the partitions, copy the source query, and run it in SSMS. Does it return the required data?
      Alex

  • Thanks Alex.

    Yes, I checked the source queries of all partition and those are retreiving expected rows when run in SSMS.

    Yesterday, what I did. I restored OLTP production backup until 31st July 2015 and restored current OLAP cube from production as well.

    when I open the cube in BIDS and browse, I was able to see data in expected measures.

    However, when I pointed data source to development database and process cube full and again browse, data disappeared from measures.

    One thing, I noticed that data is empty for only those measure groups which having partition otherwise non partition measure groups are fine(I can see data when I drag any measure from those groups)

    I tried changing processing setting because in Dev it was parrellel but in production cube process through SSIS package where it has been set sequential but it didn’t work either.

    Also, I tried to point production OLTP and re-processed cube but it result is same.

    Do you think it could be SQL server 2008R2 issue or bids2008R2? I checked both database version and those are exact same.

    Looking forward to hear from you.

    Thanks,
    Nilesh

  • Hi Alex, just to add up…..

    Last night I kicked off Cube from BIDS pointing to production OLTP and now I checked browsed cube and all measures are showing data.

    It means problem with development data or whatever data I have restored until 31st July 2015 in development from productions was broken relationship……… I think, if I restored up to date data from prod , it should work

    Let me know your thoughts ..

    Thanks,
    Niel

    • The only thing that makes sense is either
      – different source data in both databases
      – incorrect connection string settings (pointing to wrong server/database)
      – problems with the partition queries (not the measure group source, but the individual partition source)

      If you’ve been through all of those and none are an issue, then I’d put a profile trace on the source database whilst you process one of the problematic partitions, as well as adding PerfMon counters on ‘Processing/Total rows read’ and ‘Processing/Total rows written’. Between them they should shed some light on what’s going on, or at least point you in the right direction.

  • Hi Alex,

    i’m so new in MDX and this post has been helpfull in someways although my issue is not even the same as the topic of the post, but i’m sure you can help me.

    i need to calculate de percentage of items that a brand has in relation to the total percentage of items of the category to which it belongs, something like this in SQL:

    select p.idCategory, p.idBrand, (sum(p.num_items)/ Cat_num_items)*100
    from fact_table p
    group by p.idCategory, p.idBrand;

    CAT | BRAND | %
    ————————-
    A|X|49
    A|Y|51
    B|Z|80
    B|W|1
    B|S |19
    C|Z|75
    C|S |24
    C|T|1

    I have 3 dimesions Category, Brand and Period, and [Measures].[Brand_SumItems] aggregating a sum of num_itemsOfBrand field and [Measures].[Category_SumItems] aggregating a sum of num_itemsOfCategory field and finally a Calculated member with formula: [Measures].[Brand_SumItems].Value/[Measures].[Category_SumItems].Value)* 100

    This does not work because i loose the relation between brand and the category that it belongs, making a global calculation with total number of items by brand and total number of items by category separatly (gives results over 100%)

    The thing is that in the fact table i already have a field num_itemsOfCategory, that has the total number of items for the category of the brand selected so i don’t need to do a sum in this [Measures].[Category_SumItems], i just need the value in the formula but i don’t know how to do that so the calculated member should look something like [Measures].[Brand_SumItems].Value/Cat_num_items_FieldValue)* 100

    I’m sure i probably have design problems with my fact table or also in mdx cube modeling and i can change them in order to make them work, please help me.

    thank you in advance and please forgive if this is such a basic cuestion but i’m very new on this..

    PS: after that i have to cross it with periods of time (year,month,past 6months, past 12months) and make a top ten values sorted desc. But i’ll think about this later, first i need to get the percentage calculation so i can cross it later. please give me some guide..

    • Hi Yoanna

      The easiest solution to this is to fix the data model. If there is a hierarchy relationship between Brand and Category then they should be in the same dimension, with a hierarchy between them.
      Then you can simply use [Measures].[x] / ([Measures].[x], [Brand].[Brand Hierarchy].CurrentMember.Parent)

      If your data model is not suitable then you’ll end up in a world of pain, take every effort to get the model right instead of battling with getting MDX to try and fix the problem – you’ll end up fixing the data model at some point anyway.

      Regards
      Alex

      • Hi Alex! thanks for your quick response.. I tried what you suggested and it made a lot of sense, it gave me more understanding of what i’m looking for, but i realize that my issue now is not in the number of items by category but in the number of items by brand because the relation between them is “many to many”, a brand could be related to more than 1 category as in the example that i posted (brand=Z). So the measure where I get the number of items [Measures].[x] by brand is not considering that, just the global sum of items by brand without taking in account the category to which it belongs.

        In the fact table now i have: CAT| BRAND | Num_Items, and i need to do this:

        CAT| BRAND | Num_Items | Formula | PERCENTAGE
        B | Z | 800 | (800/(800+200))*100 | 80
        B | S | 200 | (200/(800+200))*100 | 20

        C | Z | 750 | (750/(750+250))*100 | 75
        C | S | 250 | (250/(750+250))*100 | 25

        Right now using what you suggested (that was very helpfull) i get this result (800+750/(800+200)*100 = 155%. How could i fix this? thank you so much in advance!

      • I forgot to tell you, to get that result i changed the dimensions, i got just one dimension with 2 hierarchies (Category and Brand) and the measure you suggested.. I was thinking maybe changing the order of the hierarchies making Brand the parent of Category and arranging the calculated member as something like:

        ([Measures].[x], [Dimension].[Category Hierarchy].CurrentMember.Parent) / ([Measures].[x])

        since the currentMember is the category, but is not working so far, i’m totally missing something, i’ll keep researching .. appreciate any other tips you may have.. thx 🙂

  • Hi, I have to admit that I dit not read all of this post. I am quite new to MDX but I want to do exactly what you describe in the example at the top and if I do it as you describe, it works.
    What I do not understand is why this does not work. I would have expected that by working with (All) it would work for all hierarchies in the date dimension, but it doesn’t. It only works with the Calender Year Month and not with the Fiscal Year Month…
    WITH MEMBER [Measures].[Sold To Date] AS ‘Sum(PeriodsToDate([Date].[(All)]),[Measures].[Sales Qty])’
    SELECT
    {[Measures].[Sales Qty],[Measures].[Sold To Date]} ON COLUMNS,
    [Date].[Calendar Year Month].Members ON ROWS
    FROM [MyCube]

  • Hi Alex,

    I have been working on a solution proposed by Sal. Followings is the article I am referring to:

    https://saldeloera.wordpress.com/2013/02/07/ssas-how-to-create-dynamic-time-calculations-to-reuse-over-any-measure/

    I have tried to reach Sal but could not get hold of him. Maybe you can help please.

    In his example Sal is using a separate table to dynamically calculate YTD values of all the measure that are included in the query.

    In the example he has used [Date Calculations].[Date Calculations].[Selected Date] but he has not set it’s value anywhere.

    Qs 1:

    Please help me understand what is the purpose of putting [Date Calculations].[Date Calculations].[Selected Date] and how it is getting the value of the current measure automatically.

    [Date Calculations].[Date Calculations].[YTD] =
    AGGREGATE
    (
    {[Date Calculations].[Date Calculations].[Selected Date]} *
    PERIODSTODATE
    (
    [Date].[Calendar Hierarchy].[Calendar Year],
    [Date].[Calendar Hierarchy].CURRENTMEMBER
    )
    );

    Qs 2:

    Where is [Date Calculations].[Date Calculations].[Selected Date] getting it’s value?

    Qs 3:

    What do I have to do to hide the [Selected Date] from showing in the result?

    Once again Alex, thank you in advance for helping me understand the tips and tricks of MDX scripts. Your articles and sharing have been very helpful to me as I learn the SSAS ways of data analysis.

    Thanks
    Joy

    • Sorry forgot to check the notify me checkbox… 🙂

    • Hi Joy

      The purpose of this Date calculation dimension is to allow you to easily select date calculations by selecting a member of this dimension. There does however need to be a base member, on which no calculations are applied. This shows the number without any date calculations, i.e. as if this dimension didn’t exist. This is the [Selected Date] member.
      Other calculations are then applied on top of the [Selected Date] member.

      [Date Calculations].[Date Calculations].[Selected Date] should be set to be the default member of the [Date Calculations].[Date Calculations] attribute, to save you from having to select it manually every time.

      Once you set it as the default member, you no longer need to select it in your pivots, and so it will not show the title. It will only show once you add it into your pivot, at which point you need it in order to differentiate the values from YTD etc.

      Hope this helps
      Alex

  • Hi Alex
    I’m Using Funcation Like Aggregate({NULL:[Date….CurrentMember]},…Count)
    But When I drill down in each Dimention its so Slow,

    • The performance issues you experience won’t be caused by this MDX approach, but by an underlying problem in the cube.
      Cube performance issues are usually caused by:
      – Poor design
      – Poor partitioning
      – Missing attribute relationships
      – Poor aggregation design
      – Insufficient hardware

      There’s no way to offer any more detail without seeing your cube implementation, but if you review all of the above then you’ll hopefully find the problem.
      Thanks
      Alex

  • Thanks Alex,
    i have a cube contain 4 Dimensions:
    1. Equipment
    2. Customer
    3. Dates
    4. Location
    i’d like to get the total number of equipment on Date hierarchy and drill down on Equipment Type, Customer Type, Location Type

    i use 1 for active and -1 for deactive Equipment in ETL Process and have a one measure to SUM this,
    i use above aggregation , when just browse on Date hierarchy , it’s good, but when i add other dimension, it’s so slow.

    i have no partition and no aggregations.

    • “i have no partition and no aggregations.”

      This will likely be the cause of your performance problems. SSAS needs partitions and aggregations to perform well.

  • yeah, your right , but in this way, i would like to calculate from start to Now, how can i partition it ? in date? no difference because it calculate total number on all of partition .

    • Yes, partition by date, week, month – whatever is most appropriate for your data.

      Even though you’re accessing all dates, it will make a difference.

      There may also be design problems in your cube which are impacting performance, but I can’t comment on that without seeing your code.

  • Alex, this is a remarkable thread, I admire your continued interaction with everyone.

    I’m trying to implement a Date Tool dimension to allow for aggregating various measures in my cube across several date dimensions. I have studied this thread and keep hitting the wall–can you offer some help?

    When deployed, this calculation only successfully applies to the Production Date dimension in my Excel pivot table. If I swap the order and put Production Date first and then Service Date, then Service Date will work.

    SCOPE ([Date Tool].[Calc].[Year To Date]);
    THIS = AGGREGATE (
    YTD ([Service Date].[Service Date Hierarchy].CurrentMember)
    , [Date Tool].[Calc].DefaultMember
    );
    THIS = AGGREGATE (
    YTD ([Production Date].[Production Date Hierarchy].CurrentMember)
    , [Date Tool].[Calc].DefaultMember
    );

    END SCOPE;

    My problem seems very similar to this comment: https://www.purplefrogsystems.com/blog/2010/08/mdx-calculated-member-spanning-multiple-date-dimensions/#comment-1593

    I tried to fit your solution into my code but nothing worked. Any assistance appreciated.

    • The problem is that both of the statements will always execute, so the second one will always override the first.
      You need to find a way of enhancing the SCOPE to narrow things down so that the relevant calculations only execute when you want them to.

      You could try something like this:

      SCOPE ([Date Tool].[Calc].[Year To Date]);
      THIS = AGGREGATE (
      YTD ([Service Date].[Service Date Hierarchy].CurrentMember)
      , [Date Tool].[Calc].DefaultMember
      );
      END SCOPE;

      SCOPE ([Date Tool].[Calc].[Year To Date], [Service Date].[Service Date Hierarchy].[All]);
      THIS = AGGREGATE (
      YTD ([Production Date].[Production Date Hierarchy].CurrentMember)
      , [Date Tool].[Calc].DefaultMember
      );
      END SCOPE;

      This starts by using the Service Date dimension, but if the [All] member of the service date dimension is selected, then it will switch to using the Production Date calculation instead.

      • It works as you described, thank you very much for the input.

      • Alex, it turns out there are twelve different date hierarchies in the cube that will need to work with my Date Tool. Am I facing the task of creating SCOPE statements for each permutation of ALL members across the twelve date dimensions, or do you see a more elegant approach?

        • Yes you would need to do exactly that, but first decide the priority order or logic of the dimensions. I.e. If a user selects from two date dimensions what should happen? Just use the highest priority dimension? Or try and use both? This will impact how you approach the code.

  • Wow, this is quite a long thread. I tried to browse through the thread to see if my question has been answered but I am not sure. Here is my question:
    I I want to span multiple MEMBERS and not hierarchies it seems that the MDX is limited since it is doing CurrentMember. When I select multiple members then my count ends up being -1. Is this a limitation on the MDX? Is there a workaround to make work with multiple members selected like Jan, Feb and Mar for the same date hierarchy?
    Thanks in advance.

    • You’re right, the CURRENTMEMBER solution will only work when there is a single member selected, it does not support a multi-select.
      For that you’d have to come up with another solution, and it will get more complicated. You can work with multi-selects inside MDX calculations using the EXISTING function, so as a first thought maybe look at using FILTER with EXISTING to see if you can build the set of members you need.

  • Hi, Alex. I’ve seen all your comments and my word “you are cool master” 🙂
    i just want to ask you recommend me usefull booj or maybe video lessons about MDX language to understand differents between hierarhy and current member, what functions works with hierarchy and currentMember? descadans, tail, ytd, closeperiod etc.
    i just want to figure out 🙂
    thank you, body.
    Roman, Ukraine Kyiv.

  • Alex, I have literally NEVER in my life read an entire blog , perhaps an entire web page for tech help. This long ass crazy in depth tech support is insane! I seriously cant believe how much you willing help people for free. You are a good guy with a lot to offer, thanks dude.
    I actually came here cuz I jumped into mdx like “how hard could it be” since ive been in sql for-ever. Shocked at my hurdles when I just want to show stupid months across the columns and years in the rows for Year-to-date calc. I had no idea this wasnt an out of the box function. So I have copied a ton of snips that look like what im dealing with and i am sure it will help, so thx again

  • Hi alex, after reading your post I haveimplement a scenario in mdx
    find out the number of complaint in the reporting month like

    select count(*) from t where complint_month_year = 08215 and resolution_month_year >08215

    WITH MEMBER MEASURES.[PENDING_AT_BEG]
    AS

    AGGREGATE(
    (
    {
    LINKMEMBER([Dim Date].[WEEK_Hierarchy].currentmember,[COMPLAINT_DATE].[WEEK_Hierarchy]
    )
    }
    * {
    LINKMEMBER([Dim Date].[WEEK_Hierarchy].CURRENTMEMBER.nextmember, [RESOLUTION_DATE].[WEEK_Hierarchy]
    )
    :NULL}
    )
    ,[Measures].[COUNT_COMPLAINTS]
    )

    SELECT { MEASURES.[PENDING_AT_BEG]} ON 0,
    NON EMPTY{([Dim Date].[WEEK_Hierarchy].[Month Name] ) } ON 1 FROM

    (SELECT (STRTOMEMBER(@YEAR, CONSTRAINED)) ON COLUMNS FROM
    (SELECT (STRTOMEMBER(@MONTH, CONSTRAINED)) ON COLUMNS

    FROM [CUBE_xxx]))

    but it showing null all time

  • Hi,

    I have a performance issue on this mdx:
    with MEMBER [e]
    AS
    AGGREGATE({NULL:LINKMEMBER([Record Date].[Date Hierarchy].currentmember
    ,[START Date].[Date Hierarchy])}*
    {LINKMEMBER([Record Date].[Date Hierarchy].currentmember
    , [END Date].[Date Hierarchy]):NULL}
    , [Measures].[Count of Preference])

    SELECT {[e]} on 0
    from [Cube]
    where ([Record Date].[Date Hierarchy].[Day].&[2016-01-11T00:00:00])

    This takes 11 mins which is not acceptable. any idea why it is so slow.

  • Hi Alex,

    I am stuck in a very similar situation for rolling periods member of period utility dimension which I want to evaluate over two hieraechies, fiscal and calendar (for day, month and quarter level for both hiearchies). Issue is only one of them is being evluated at one time.

    Here is sample code

    Scope([Period].[Period].[Rolling 12 months]);

    Scope([Date].[Calendar].[Calendar Month]);

    ([Period].[Period].[Rolling 12 months])= Aggregate([Period].[Period].&[0] * LASTPERIODS( 12,[Date].[Calendar].CurrentMember));

    End Scope;

    Scope([Date].[Fiscal].[Fiscal Period]);

    ([Period].[Period].[Rolling 12 months])= Aggregate([Period].[Period].&[0] * LASTPERIODS( 12,[Date].[Fiscal].CurrentMember));

    End Scope;

    End Scope;

    Similar code applies for day and quarter as well for both hierarchies, however only one of them runs at one time, I believe we need to set default behaviour and scope the other one.

    Please advise.

    • Yes you’re right, you need to set the default code to work with one of the hierarchies, and then scope the calculation to use the other when the first has ‘all’ selected.
      So you could have the default code using [Date].[Calendar] and then scope the exception for [Date].[Calendar].[all] to use [Date].[Fiscal] instead.
      Regards
      Alex

  • Hi Alex,

    I have a problem with data displaying and calculation matter. The following MDX displays all the positive total (MEASURES.Total) and if any negative total then converted them into + value and are displayed accordingly. However, my requirement is in Hierarchy Marketing i have Expense and Income, i want to display all expenses and those income have negative value and exclude positive income from calculation. So the TOTAL value will be all expenses+ (-) income per Traders. But currently it is being calculated as separately i mean, for example trader (barlen) shows 2 total 1 for expense and another for income of marketing.
    could you please find that solution ? I would be really appreciate your help, because last 2 weeks i have been trying to sort out this problem.

    WITH

    MEMBER [Measures].[Negative Total] AS
    CASE
    WHEN ([Measures].[Total]) < 0 THEN [Measures].[Total]* -1
    ELSE [Measures].[Total]
    END

    MEMBER
    [Dim Account].[Account Activity].TOTAL AS
    [Dim Account].[Account Activity].[All]
    //SUM(Traders,[Measures].[Total])

    MEMBER [Dim Branch].[Trading As].[Pedders Suspension]
    AS
    Aggregate
    (
    {
    [Dim Branch].[Trading As].&[PED]&[Pedders Suspension]
    ,
    [Dim Branch].[Trading As].&[PHO]&[Pedders Hornsby]
    }
    )

    SET Traders AS
    {
    [Dim Branch].[Trading As].&[MEC]&[Autoco Mechanical]
    ,
    [Dim Branch].[Trading As].&[RSA]&[Roadside Assist]
    ,
    [Dim Branch].[Trading As].&[BSE]&[B Select]
    ,
    [Dim Branch].[Trading As].[Pedders Suspension]
    ,
    [Dim Branch].[Trading As].&[ASR]&[Autoco Smash Repair]
    ,
    [Dim Branch].[Trading As].&[BAR]&[Barlens Event Hire]
    }

    SELECT
    NON EMPTY
    {
    [Measures].[Negative Total], [Measures].[Total_Income_Percent]
    }
    ON COLUMNS
    ,
    NON EMPTY
    (
    {
    (
    [Dim Account].[HierarchyMarketing].[Account Type].&[Income]
    *
    {
    VisualTotals({[Dim Account].[Account Activity].[All]})
    }
    )
    ,
    {
    [Dim Account].[HierarchyMarketing].[Account Marketing].&[Expenses]&[Marketing]
    ,
    [Dim Account].[HierarchyMarketing].[Account Marketing].&[Income]&[Marketing]
    }
    *
    [Dim Account].[Account Activity].[Account Activity].MEMBERS
    ,
    {
    [Dim Account].[HierarchyMarketing].[Account Marketing].&[Expenses]&[Marketing]
    ,
    [Dim Account].[HierarchyMarketing].[Account Marketing].&[Income]&[Marketing]
    }
    *
    [Dim Account].[Account Activity].TOTAL
    }
    ,
    [Dim Fiscal Year].[HierarchyFiscal].[E Month].&[2016]&[December]

    , NONEMPTY([Dim Scenario].[Scenario Name].&[Actual])
    ,
    Traders
    )
    ON ROWS
    FROM [CubeProfitLoss]

  • Hi,

    I’m using Tail function to get the date of last non empty value, But this gives poor performance and the report is taking more than 3 minutes to display the result. Please find the below query.. Can you suggest an alternative solution for this?

    member [measures].[lastused] as Extract(tail(filter({[Sale Date].[Date].children },
    not isempty([Measures].[Sales Item Count])),
    1),
    [Sale Date].[Date]).Item(0).Member_caption

    • Hi Gowri

      I’d rewrite the expression as

      member [measures].[lastused] as
      tail(NONEMPTY([Sale Date].[Date].children, [Measures].[Sales Item Count]),1).item(0).Member_caption

      i.e. Using NonEmpty instead of Filter. And the extract can also be simplified.

      I’m not sure how efficiently SSAS interprets extract & filter compared with the alternative, but hopefully it might improve things for you.
      Alex

  • Hi Alex

    I am new to MDX. I have a cube with few dimensions. Report Date and Account are two of them. One account id may come under multiple Report dates. I need to find minimum report date for every accountid. Any ideas? I really do appreciate the help.

    Thanks
    Anubhav

    • Hi Anubhav

      You need something like this

      WITH MEMBER [Measures].[Account Minimum Date] AS
      NONEMPTY([Reporting Date].[Date].[Date].MEMBERS, [Measures].[Sales]).item(0).MEMBER_NAME
      SELECT [Measures].[Account Minimum Date] ON 0,
      NON EMPTY [Account id].[Account id].[Account id].MEMBERS ON 1
      FROM [Cube]

      i.e. Create the Account Minimum Date as a measure which finds all of the non empty dates, and takes the name of the first item in the resulting set. Note that you need to provide a measure name in the nonempty statement, which lets the cube know which measure you’re using to determine activity, i.e. [measures].[Sales] or something.

      Regards
      Alex

      • Hi Alex

        Thanks a lot for the help.

        i tried the above query

        WITH MEMBER [Measures].[Account Minimum Date] AS
        NONEMPTY([REPORT DATE].[Report Date_1].MEMBERS, [Measures].[# Units]).item(0).MEMBER_NAME
        SELECT [Measures].[Account Minimum Date] ON 0,
        NON EMPTY [CONTRACT].[Agreement ID].members ON 1
        FROM [MyCube]

        but i am getting “All” for [Account Minimum Date] for every accountid(ie agreementid).
        my Report Date_1 is in format “YYYYMMDD”
        Any other pointers to get the date instead of “All” values?

        • This is because [REPORT DATE].[Report Date_1].MEMBERS includes all members of the hierarchy, ALL and everything below it.
          If you replace it with [REPORT DATE].[Report Date_1].[Report Date_1].MEMBERS then it skips past the hierarchy and returns just the actual members.

          So change your query to

          WITH MEMBER [Measures].[Account Minimum Date] AS
          NONEMPTY([REPORT DATE].[Report Date_1].[Report Date_1].MEMBERS, [Measures].[# Units]).item(0).MEMBER_NAME
          SELECT [Measures].[Account Minimum Date] ON 0,
          NON EMPTY [CONTRACT].[Agreement ID].[Agreement ID].members ON 1
          FROM [MyCube]

          • Thank you Alex, it worked like a charm 🙂

            Can you please help me to understand how it is working. Is it the .item(0) that constrains the output to the earliest/minimum REPORT DATE each intersection with Agreement ID. Does this assume an ascending sort order on REPORT DATE? What if i wanted the last/maximum date or had a custom sort?

          • Hi Anubhav

            All attributes in a cube have a specific pre-determined order, the cube relies on this for a lot of functionality. When you’re designing a cube it’s your job to set the ordering of each attribute correctly, whether alphabetically, numerically, by date, or by some other method (i.e. month name is none of the above).

            Once you do this, then you can take the first member of the set by using “.item(0)”, or the last member by using “TAIL(xxx, 1).item(0)”

            Regards
            Alex

  • Hi Alex,

    this page is awesome, very helpfull. But I would like to ask you for help.
    My problem is that I would like to calculate measure based on selected date dimension in e.g. Excel. My solution has two date dimension Posting Date and Shipment Date and I would like to calculate time comparison year to date on Deliveried Quantity measure .

    Based on previous posts I tried a lot of variations, for example:

    SCOPE([Measures].[Deliveried Quantity],[Date].[Date YMD]);
    this = AGGREGATE(SUM(YTD([Date].[Date YMD].CurrentMember),
    [Measures].[Deliveried Quantity].DefaultMember));
    END SCOPE;

    SCOPE([Measures].[Deliveried Quantity],[Shipment Date].[Date YMD]);
    this = AGGREGATE(SUM(YTD([Shipment Date].[Date YMD].CurrentMember),
    [Measures].[Deliveried Quantity].DefaultMember));
    END SCOPE;

    When I test it in Excel, it produces a #VALUE! error.

    Anything you can suggest would be really useful!
    Thank you.

    Best regards,

    Martin

    • Hi Martin
      A couple of things to look at here:
      Firstly SUM is a type of AGGREGATE, so you don’t need them both in the same query.
      Then there’s a commonly encountered hurdle with SCOPE. No matter what, there is always a member of every attribute selected, whether you have selected one or not. If you haven’t selected one then [All] is selected by default.
      So when you write SCOPE([Measures].[Deliveried Quantity],[Shipment Date].[Date YMD]), there will always be a member of [Shipment Date].[Date YMD] selected, so the scoped assignment will always be triggered.
      So try replacing it with something like:
      SCOPE([Measures].[Deliveried Quantity], DESCENDANTS([Shipment Date].[Date YMD],,AFTER))
      Which should exclude the [All] member and make it work.
      Regards
      Alex

  • Hello Alex,

    Thank you for your support . I need one help from you regarding fiscal year and calendar year.
    we have existing Mdx query where initially they are using Fiscal year hierarchy for calculating YTD now they have added one more hierarchy such as calendar year. now they want to browse YTD value on both hierarchy.

    i have used scope to display the value which is listed below
    scope [Measures].[YTD Actual GWP Sum] ;
    scope ([dimTimeDimension].[EGY Calendar Year].members,[dimTimeDimension].[Calendar Year].[Year].members);
    this = SUM(
    YTD([dimTimeDimension].[Calendar Year].CURRENTMEMBER)
    ,[Measures].[Gross Written Premium]
    );
    end scope;
    scope ([dimTimeDimension].[Calendar Year].members,[dimTimeDimension].[EGY Calendar Year].[Year].members);
    this = SUM(
    YTD([dimTimeDimension].[EGY Calendar Year].CURRENTMEMBER)
    ,[Measures].[Gross Written Premium]
    );
    end scope;

    end scope;

    since we have multiple YTD measure so its difficult for us to implement scope in each measure so do we have any other way to implement this one without any huge modification?

    Thanks a lot in advance

  • Hi Alex -I have a simple Year-Period hierarchy and need a rolling figure for the last 52 periods e.g. rolling aggregate figure for Year 15 Period 32 to Year 16 Period 32. I can easily produce an aggregate figure for the full Year-Period hierarchy but need it to reset to zero at start of last 52 periods.
    Can you help?

    • Hi Chris
      You just need to identify the last period from whatever level of the date hierarchy is currently selected, and then sum all transactons from that period to lag(11) of that period. That will give you a rolling 12 period window.
      The following code does this for the AdventureWorks cube.

      WITH MEMBER [Measures].[Rolling 12 Periods] AS
      SUM(
      {TAIL(DESCENDANTS([Date].[Calendar].CURRENTMEMBER, [Date].[Calendar].[Month], SELF),1).Item(0).LAG(11)
      :TAIL(DESCENDANTS([Date].[Calendar].CURRENTMEMBER, [Date].[Calendar].[Month], SELF),1).Item(0)}
      ,[Measures].[Internet Sales Amount]
      )
      SELECT {[Measures].[Internet Sales Amount], [Measures].[Rolling 12 Periods]} ON 0,
      NON EMPTY DESCENDANTS([Date].[Calendar], [Date].[Calendar].[Month], SELF_AND_BEFORE) ON 1
      FROM [Adventure Works]

      Regards
      Alex

  • Hi Alex,
    first of all many thanks to this amazing blog – i think you are helping a lot of people! 🙂 I hope you can help me out as well. Here is the case:
    I have a fact table which contains among other things a PERIOD_ID (20170930, 20180230, …), ACCOUNT(Assets, Liabilites), and FORECAST INCOME. After the user is setting a so called Report Date (2016-09-30) the user should be able to see 5 Buckets which are summing up the next 12 Months after the Report Date. So when Report Date equals 2016-09-30, the sum of Date 2016-10-31(Reporting Date + 1 month) until 2017-09-30 should be displayed under “Dimension” 1Y. This should go up to 5Y (Sum of Values between 2021-10-31 to 2022-09-30 ).

    I hope this is clear and you can give me a hint how i can do this 🙂

    Thanks&Best regards
    Alex

    • Hi Alex
      I’d use a utility date dimension to achieve this. Create a new dimension with a single attribute containing 5 members (1Y to 5Y). Then use scope to override these member values with a calculation of the 1Y-5Y totals.
      The following link is a good walkthrough as a starting point, you’ll just need to change the calculations to select the relevant time period.
      https://intelligentsql.wordpress.com/2013/02/15/ssas-date-utility-dimensions-pre-aggregating-over-time/
      Alex

  • Hi,

    I tried recreating this formula using ‘*’ in formula for a calculated measure in mondrian cube. I need to pick up the dimension that is used at run time. I tried using * but it is not working in Pentaho mondrian cube. Can you please mention what is the alternative for * in mondrian cube?

  • Some three years ago when designing our business main OLAP cube we faced this problem, our company analyzes data both monthly and weekly. After weeks of searching dozens of blogs and forums we ended up with a quite complex solution. For example to allow comparation with previous year:

    CREATE MEMBER CURRENTCUBE.[Measures].[PY Value]
    AS iif([Dim Time].[Weeks].CurrentMember IS [Dim Time].[Weeks].[All],
    ([Measures].[Value], ParallelPeriod([Dim Time].[Months].[Year])),
    ([Measures].[Value], ParallelPeriod([Dim Time].[Weeks].[ISO Year])))

    It works for “almost” all situations.

    A couple of days ago I discovered by accident this post and I was delighted! Wow! That’s far more simple and generic, let’s try it.

    I have not been able to make it work, neither

    CREATE MEMBER CURRENTCUBE.[Measures].[PY Value]
    as (ParallelPeriod([Dim Time].[Months].[Year] * [Dim Time].[Weeks].[ISO Year])),
    [Measures].Value])

    nor

    CREATE MEMBER CURRENTCUBE.[Measures].[PY Value]
    as (ParallelPeriod([Dim Time].[Months].[Year]) * ParallelPeriod([Dim Time].[Weeks].[ISO Year])),
    [Measures].Value])

    works.

    Any idea on what are we doing wrong

  • Hi Alex,

    I am trying to run one MDX query using Calculation. But it is showing #Error in Column. Could you please help me to correct it?

    WITH MEMBER [Measures].[Cal] AS
    CASE WHEN [DimOutwardTypes].[OutwardTypeID].[OutwardTypeID].currentmember is [DimOutwardTypes].[OutwardTypeID].&[1] or [DimOutwardTypes].[OutwardTypeID].[OutwardTypeID].currentmember is [DimOutwardTypes].[OutwardTypeID].&[3] THEN
    1
    ELSE 2
    END SELECT NON EMPTY { [Measures].[IAmount], [Measures].[SAmount], [Measures].[CAmount], [Measures].[XAmount], [Measures].[Cal] }
    ON COLUMNS, NON EMPTY { ([DimOutwardTypes].[OutwardTypeID].[OutwardTypeID].ALLMEMBERS ) }
    ON ROWS FROM [cubeClient]

    Thanks,
    Hiren

    • Hi Hiren

      Try changing the 2nd line to
      CASE WHEN [DimOutwardTypes].[OutwardTypeID].currentmember is…

      Regards
      Alex

  • Hi All,

    I need to create the ly measures based on whatever date attribute is selected. e.g now it is coupled with the date hierarchy and it only works when i have the date hierarchy in rows or columns. I need it to work for both hierarchy as well as for if the year, e.g if i use the year attribute only it should work as well as for any other date attribute eg. year, month, week etc. Any guesses?

    thanks
    Roan.

    • Your best option is to create your LY measure based on the hierarchy. Then add a SCOPE() statement to override the functionality different logic based on each different date attribute that you want to include.

      You therefore end up with multiple MDX calculations, each relevant for different date attributes.

      Thanks
      Alex

  • hi ,

    i am new in SSAS , i have developed a project and facing a problem in below code , i am very inspire by “Alex”.
    can any one guide me in my code?

    Scope([Measures].[Unit- Closing Stock]);
    This = sum(periodstodate(null:[Time].[date].CurrentMember *
    null:[Time].[Day Numberof Week].CurrentMember *
    null:[Time].[Day Of Month AD].CurrentMember *
    null:[Time].[Day Of Month Islamic].CurrentMember *
    null:[Time].[Day Of Week].CurrentMember *
    null:[Time].[Day Of Year].CurrentMember *
    null:[Time].[Islamic Date].CurrentMember *
    null:[Time].[Islamic Day Of Month].CurrentMember *
    null:[Time].[Islamic Month Name].CurrentMember *
    null:[Time].[Islamic Month Number].CurrentMember *
    null:[Time].[Quarter].CurrentMember *
    null:[Time].[Week Description].CurrentMember *
    null:[Time].[Week Number].CurrentMember *
    null:[Time].[Year].CurrentMember *
    null:[Time].[Month].CurrentMember)
    ,[Measures].[Movement]); FORMAT_STRING ( This ) = “#,#”;
    End Scope;

    this code showing result #VALUE!

    • Hi Abid
      You need to remove the periodstodate() function, as you’re achieving this functionality yourself by using the {null:[Time].[xxx].CurrentMember} approach, which effectively does ‘All Members To Date’.
      Thanks
      Alex

  • ****How to write MDX/DAX for Prior Year Comparison for 445 or custom calendar​
    Hi Alex,

    How are you? I’m currently working on AAS Tabular model where I need to create some one the calculations with DAX. I’m struggling to create formula for custom/445 calendar which different than regular calendar where one week can have different number of days in current year vs prior year .

    As an example lets say you have 10 days in week 52 of 2017 and 8 days in week 52 of 2016, which 8 days we will map those?

    I am not sure since number of days in week vary year by year how will one be able to map less days in week of prior year to more days in week of current year, as an example lets say you have 8 days in week 52 of current year and 10 days in week 52 of prior year, There are two less rows in current year so we will have to add phantom rows.

    Would you please help me on this or give me one example of DAX/MDX formula you have already been experienced such scenario. i’m fine with MDX query if you doesn’t have DAX.

    Will appreciate your help.

    Cheers,
    Niel

Leave a Reply

Your email address will not be published. Required fields are marked *

HTML tags are not allowed.

344,242 Spambots Blocked by Simple Comments

The Frog Blog

I'm Alex Whittles.

I specialise in designing and implementing SQL Server business intelligence solutions, and this is my blog! Just a collection of thoughts, techniques and ramblings on SQL Server, Cubes, Data Warehouses, MDX, DAX and whatever else comes to mind.

Data Platform MVP

Frog Blog Out
twitter
rssicon