locked
Calculation based on specific value RRS feed

  • Question

  • In VS2015, I've run into a issue with a calculation that I need some guidance on.

    Dimensions
    Account
      AccountID
      AccountCode
      ...

    Calendar
      Fiscal Period (i.e. 201701, 201702 to 201812)
      Fiscal Period Number (i.e. 1, 2, to 12)
      ...

    Finanical Map
      MapID
      MapName
      AccountCode
      Income Statement Budget Sign
      Income Statement Actual Sign
      ...

    Fact
    ProfitLoss
      Fiscal Period
      Current Month Amt
      Last Month Amt
      YTD Amt
      P1FC1 Amt
      P2FC1 Amt
      P3FC1 Amt
      P1FC2 Amt
      P2FC2 Amt
      P3FC2 Amt
      ...

    Depending on which Financial Map the user selects the correct sign needs to be applied. An account code could have a different sign depending on the financial map (i.e. EBITDA, Balance Sheet, Trial Balance, etc.).

    I can create the following calculations, which appear to work correctly -

    CREATE MEMBER CURRENTCUBE.[Measures].[LM Current]
     AS IIF(
        IsLeaf([Financial Map].[Income Statement Actual Sign].CurrentMember),
        [Measures].[Last Month Amt] * [Financial Map].[Income Statement Actual Sign].CurrentMember.Properties("key"),
        Sum([Financial Map].[Income Statement Actual Sign].Children, [Measures].[Last Month Amt] * [Financial Map].[Income Statement Actual Sign].CurrentMember.Properties("key"))
       ), 
    FORMAT_STRING = "Currency", 
    VISIBLE = 1 ,  DISPLAY_FOLDER = 'Actual';


    CREATE MEMBER CURRENTCUBE.[Measures].[Current]
     AS IIF(
        IsLeaf([Financial Map].[Income Statement Actual Sign].CurrentMember),
        [Measures].[Current Month Amt] * [Financial Map].[Income Statement Actual Sign].CurrentMember.Properties("key"),
        Sum([Financial Map].[Income Statement Actual Sign].Children, [Measures].[Current Amount Amt] * [Financial Map].[Income Statement Actual Sign].CurrentMember.Properties("key"))
       ), 
    FORMAT_STRING = "Currency", 
    VISIBLE = 1 ,  DISPLAY_FOLDER = 'Actual';  

    But I'm having a problem with the calculations that are dependent on the period.

    Here is the pseudo code example for the forecast for period 1.

    CASE [Calendar].[Fiscal Period Number] <= 2 THEN [Measures].[P1FC1 Amt] * [Financial Map].[Income Statement Actual Sign]
      ELSE [Measures].[P1FC1 Amt] * [Financial Map].[Income Statement Budget Sign]

    And the calculation should be summarized even if the Fiscal Period Number is selected or not ...
    So how should this be done?

    Thursday, January 18, 2018 7:15 PM

Answers

  • Hi Darren,

    Thanks for your question.

    According to your description, you can try below MDX query:

    CREATE MEMBER CURRENTCUBE.[Measures].[Forecast]
      AS IIF([Calendar].[Fiscal Period Number].CurrentMember.Member_key <= 2,
        [Measures].[P1FC1 Amt] * 
       [Financial Map].[Income Statement Actual Sign].CurrentMember.Properties("key"),
       [Measures].[P1FC1 Amt] * 
       [Financial Map].[Income Statement Budget Sign].CurrentMember.Properties("key")
           )
    
    >>>And the calculation should be summarized even if the Fiscal Period Number is selected or not ...
     So how should this be done?
    In this sceanrio, I would suggest you to create physical measures for this.

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, January 19, 2018 9:00 AM

All replies

  • Hi Darren,

    Thanks for your question.

    According to your description, you can try below MDX query:

    CREATE MEMBER CURRENTCUBE.[Measures].[Forecast]
      AS IIF([Calendar].[Fiscal Period Number].CurrentMember.Member_key <= 2,
        [Measures].[P1FC1 Amt] * 
       [Financial Map].[Income Statement Actual Sign].CurrentMember.Properties("key"),
       [Measures].[P1FC1 Amt] * 
       [Financial Map].[Income Statement Budget Sign].CurrentMember.Properties("key")
           )
    
    >>>And the calculation should be summarized even if the Fiscal Period Number is selected or not ...
     So how should this be done?
    In this sceanrio, I would suggest you to create physical measures for this.

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, January 19, 2018 9:00 AM
  • Using [Calendar].[Fiscal Period Number].CurrentMember.Member_key <= 2 I was able to get closer.

    I used that in the following -

    CREATE MEMBER CURRENTCUBE.[Measures].[Forecast]
     AS IIF([Calendar].[Fiscal Period Number].CurrentMember.Member_key <= 2,
            IIF(
                IsLeaf([Financial Map].[Income Statement Actual Sign].CurrentMember),
                [Measures].[P1FC1Amt] * [Financial Map].[Income Statement Actual Sign].CurrentMember.Properties("key"),
                Sum([Financial Map].[Income Statement Actual Sign].Children, [Measures].[P1FC1Amt] * [Financial Map].[Income Statement Actual Sign].CurrentMember.Properties("key"))
               ),
            IIF(
                IsLeaf([Financial Map].[Income Statement Budget Sign].CurrentMember),
                [Measures].[P1FC1Amt] * [Financial Map].[Income Statement Budget Sign].CurrentMember.Properties("key"),
                Sum([Financial Map].[Income Statement Budget Sign].Children, [Measures].[P1FC1Amt] * [Financial Map].[Income Statement Budget Sign].CurrentMember.Properties("key"))
               )
        ),
    FORMAT_STRING = "#,##0.00;-#,##0.00",
    VISIBLE = 1 ,  DISPLAY_FOLDER = 'Forecast';

    Right now I'm trying verify if this is working as expected and will produce the correct value.

    Monday, January 22, 2018 4:17 PM
  • Hi Darren,

    Thanks for your response.

    I am glad to know that you were able to get closer to this question.  I’m writing to follow up with you on this post. Was the problem resolved?  If it was resolved, please kindly mark the helpful replies as answers. By doing so, it will benefit all community members who are facing similar issues. Your contribution is highly appreciated.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, January 23, 2018 6:37 AM