# Calculation based on specific value • ### 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

• Hi Darren,

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,

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,