Post reply

MDX - LOOKUPVALUE / VLOOKUP?

  • November 29, 2018 at 5:18 am

    #373174

    Hi

    Given the image below, how would I go about creating 2 MDX calculated measures to create an opening and closing balance.

    Assume that I have to use a multidimensional cube and that I cannot:

    1. use Tabular cube (in that case i would have used DAX - LOOKUPVALUE)

    2. I cannot change the underlying tables, therefore I am forced to use MDX

    I know about TAIL, DESCENDANTS etc. but I need a working example please.

  • November 29, 2018 at 9:05 am

    #2014952

    Hi
    I have solved my own question.
    However, I now have an extended question...

    This calculated measure worked to obtain the previous period & category's closing balance
    IIF([Dimensions].[Period].Prevmember IS NULL, 0,
    ([Dimensions].[Period].Prevmember, [Measures].[Closing Balance]))

    Now I want to be able to identify the movements into categories ...
    for example I want to know what was previous in category A and came into category B during the last period only..

    Basically i need those IN / OUT amounts ... by looking at opening balances that were not in the current category previously, for the first period they must all be accounted for in the IN column and thereafter only based on movements into or out of categories.

    Here is my ultimate view I am after that I want to construct using MDX to compute the IN, OUT and CHANGED amounts on a Period and Category level...

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply