Custom measure calculation taking 2014 as leap year

[manohar_pappireddy]

Hi,

We have copied custom measure from one environment to other environment.

In source environment the calculation in custom measure for "Average daily balance Periodic" working fine.

Here it is considering 28 days for Feb2014.But in target environment,it is considering 29 days for Feb2014.

Time dimension members and properties  are same in both the environments.

The issue is with Only Feb month.

Any other settings we need to look for new environment/Model?

Thanks,

Manohar

[former_member186338 · Accepted solution]

Hi Manohar,

But where is the custom measure formula?

Vadim

[manohar_pappireddy · Accepted solution]

Hi Vadim,

The custom measure is in both the environments.

CPMB/CNPW963 (Days of year)is the property of time dimension.

IIF([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="Q2",

            IIF([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/CNPW963")="365",

            ((([MEASURES].[/CPMB/SDATA],CLOSINGPERIOD([%TIME%].[LEVEL02]).LAG(2))*28 + ([MEASURES].[/CPMB/SDATA],CLOSINGPERIOD([%TIME%].[LEVEL02]).LAG(1))*31 + ([MEASURES].[/CPMB/SDATA],CLOSINGPERIOD([%TIME%].[LEVEL02]).LAG(0))*30)/89),

            ((([MEASURES].[/CPMB/SDATA],CLOSINGPERIOD([%TIME%].[LEVEL02]).LAG(2))*29 + ([MEASURES].[/CPMB/SDATA],CLOSINGPERIOD([%TIME%].[LEVEL02]).LAG(1))*31 + ([MEASURES].[/CPMB/SDATA],CLOSINGPERIOD([%TIME%].[LEVEL02]).LAG(0))*30)/90)

),

Thanks,

Manohar

[former_member186338 · Accepted solution]

Please check the technical name of the property 2/CPMB/CNPW963 in the new environment...

Vadim

[manohar_pappireddy · Accepted solution]

Hi Vadim,

Your guess is correct.I too verified that before and changed with new technical name.But the issue remained.

Now I have deleted the cache,closed the BPC URL and re launched the EPM report.

Now it is working fine.
Thanks,

Manohar

[Former Member · Accepted solution]

Hi Manohar,

I have a similar requirement of getting average YTD balances for Balance sheet items. Does your formula work fine for Quarters and Year Totals. I mean do you see correct Average YTD values if you select quarters and Year Totals in report. Ideally Quarters should have same values as the last month of quarters and Total should have same value as December. Please share your measure formula code.

Thanks in Advance.

Regards

Nikhil

[former_member186338 · Accepted solution]

Hi Nikhil,

Can you provide some details - what do you mean by average YTD in your case?

Vadim

[Former Member · Accepted solution]

Hi Vadim,

I have tried putting an example in attached images. I did it in excel but looks like SCN does not allow excel attachments. I have also put the excel formulas in image. Average YTD   means

Jan                                Feb

Jan value *31/31     (Jan Value*31+Feb Value*28)/59 and so on.

For quarters and Year totals it should be last months value so for Q1 it would be same as March and for Yearly total it would be same as Dec.

Please let me know if you still need further information.

Thanks.

Regards

Nikhil

[former_member186338 · Accepted solution]

Hi Nikhil,

Then just start writing measure formula with long nested IIF:

IIF([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="...",...

I have concerns about resulting performance...

Another option is to store days in the month in some special account member and use it in the formula...

Vadim

[Former Member · Accepted solution]

Thanks Vadim. I have currently worked the same way but not able to get the correct values at time node members.

[former_member186338 · Accepted solution]

Please, show you code!

Vadim

P.S. And better in a new discussion!

[Former Member · Accepted solution]

Vadim,

I have created a new discussion for this as below. I have also put the current formula used.

http://scn.sap.com/thread/3665392

Regards

Nikhil