Custom Measure Requirement

[Former Member]

Hello everyone!

I have a requirement to create a custom measure that allows reporting across multiple years.  To be a bit more precise, consider the following example:

A new contract started in September 2012 and will end in September 2014 with the following activity: 

Period	Activity	Comment
2012.09	10,000,000	Contract entered
2013.03	3,000,000	Billings recognized via Percent of Completion accounting
2014.09	7,000,000	Contract ended (Ending Backlog down to zero)

Using a custom measure, I'd like to see the following results in my report:

Period	Activity in report	Comment
2012.09	10,000,000	Ending Backlog
2012.TOTAL	10,000,000	Ending Backlog
2013.01	10,000,000	Beginning Backlog (Carry forward logic)
2013.03	7,000,000	Remaining Backlog after billings offset
2013.TOTAL	7,000,000	Ending Backlog
2014.09	-	Ending Backlog / Contract Ended
2014.TOTAL	-	Ending Backlog

Please note:  The length of the contracts may differ depending on the terms and conditions of the contract. 

As a starting point, I created a new measures formula under UJA_MAINTAIN_MEASURE_FOMULA, named it YTD_TOTAL and maintained the following formula (but it doesn’t meet the above requirement):

MEMBER [MEASURES].[YTD_TOTAL] AS

'IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC",SUM(PERIODSTODATE([%TIME%].[LEVEL00],
[%TIME%].CURRENTMEMBER),-[MEASURES].[/CPMB/SDATA]),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP",SUM(PERIODSTODATE([%TIME%].[LEVEL00],
[%TIME%].CURRENTMEMBER),[MEASURES].[/CPMB/SDATA]),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="AST",([MEASURES].[/CPMB/SDATA],CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ",-([MEASURES].[/CPMB/SDATA],
CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])),-[MEASURES].[/CPMB/SDATA]))))';SOLVE_ORDER=3

Please let me know if the above scenario is not clear and if you need additional information from me.  All your guidance is greatly appreciated.

Thanks in advance for your time!

Best regards,

Vijay.

[Former Member]

Please start explaining from the basics:

- Is your BPC model using Periodic or YTD storage?

- Financial type model?

- Do you want this calculated for balance sheet accounts AST or LEQ type? And also P&L accounts EXP and INC types?

- Why exactly do you want to use a custom measure?

- Did you consider different use cases of the flow (subtables dimension)?

- Is it purely a reporting requirement for your customer to report on multiple years? How many reports like this are needed?

- Did you consider using just excel logic to do the same?

[Former Member]

Hi Jef,

Thanks for your response.  Please see my answers below to your questions.

- Is your BPC model using Periodic or YTD storage?

The model is a periodic model.

- Financial type model?

The model is a Financial type model.

- Do you want this calculated for balance sheet accounts AST or LEQ type? And also P&L accounts EXP and INC types?

Currently, the requirement is to only report on INC & EXP types.  However, the client may request the same treatment for the AST & LEQ types also.

- Why exactly do you want to use a custom measure?

My client has projects that could span multiple years, and uses Percent of Completion methodology for their accounting.  Therefore, to report across years, I thought that a custom measure would be the appropriate solution.

- Did you consider different use cases of the flow (subtables dimension)?

We do not use FLOW (Type S) dimension in our model.

- Is it purely a reporting requirement for your customer to report on multiple years? How many reports like this are needed?

This is purely a reporting requirement.  There are about 5 reports but the client wants the capability to create adhoc reports.

- Did you consider using just excel logic to do the same?

I actually thought about creating a new dimension member in the TIME dimension and call it something like TIME_TOTAL and roll up all the years to this member.  This may meet our requirement and maybe an easier solution. However, I am not in favor of this idea, as it doesn’t quite sound right. 

I am open to suggestions, if a custom measure is not the best solution in this case.

Thanks in advance for your time!

Best regards,

Vijay.

[former_member186338]

Hi Vijay,

The requirements are not clear, try to explain once more...

B.R. Vadim

[Former Member]

Hi Vadim,

Thanks for your response.  I will try to reword my requirement below:

Consider I have sales activity of $100 in September 2012, $200 in June 2013, and $50 in Feb 2014.  When I report on the activity, I would like to see the following results in my reports.

Year	Measure Selected	Total Activity
2012.TOTAL	YTD	100
2012.TOTAL	Inception To Date	100
2013.TOTAL	YTD	200
2013.TOTAL	Inception To Date	300
2014.TOTAL	YTD	50
2014.TOTAL	Inception To Date	350

Please note:  The "Inception To Date" measure in the table above is what I want to create.  When
selected, this measure should report activity across years (for INC & EXP accounts).

Hope the above requirement is a bit clearer to you.

Again, thanks in advance for your time.

Best regards,

Vijay.

[former_member186338]

Hi Vijay,

Start playing with something like:

SUM([%TIME%].[2006.01]:[%TIME%].CURRENTMEMBER,[MEASURES].[/CPMB/SDATA])

B.R. Vadim

[Former Member]

Hi Vadim,

Sorry for the delayed response.  I have been out for the past few days.

Per your suggestion, I tried coding the custom measure formula in this manner:

SUM([TIME].[2006.01]:[%TIME%].CURRENTMEMBER,[MEASURES].[/CPMB/SDATA])

However, when I do this the system is aggregating the values for the year totals also.

For instance, assume I have $100 in 2010.01, and $200 in 2011.05.  When I run the report for 2011.06 with this custom measure, I am getting a value of $400 ($100 for 2010.01 + $100 for 2010.TOTAL + $200 for 2011.05) instead of just the $300 ($100 for 2010.01 + $200 for 2011.05).

Is there a way for us to specify the level of the time dimension in the above formula such that the measure aggregates only the monthly values from 2006.01 to the current member?  If so, can you please offer suggestions?

If there is another way to write this formula, please let me know.

As always, thanks in advance for all your guidance.

Best regards,

Vijay.

[former_member186338]

Hi Vijay,

As far as I remember, the proposed formula was incorrect only when current TIME member was parent (quarter, year). For base members (months) it was correct for periods in multiple years. Please, test again.

B.R. Vadim

[Former Member]

Hi Vadim,

Thank you for the response.

Yes, the formula is working properly when the current member in the TIME dimension is a base member (I previously tested the formula using a year for the current member).

That said, I request your further guidance to incorporate the 2 requirements outlined below into the custom measure formula:

1.  The user would like the ability to pick any member (year, quarter or month) for the TIME dimension as the current member, and would still like to see the measure aggregating the data.  For example, if 2011.TOTAL is selected in the context, the measure should aggregate all the monthly data for 2010 and 2011.  (To get this to work, can I use the PERIODSTODATE function?  I ask because it is my understanding that the PERIODSTODATE function looks only at the current year, and does not cross over into other years)

2.  The measures formula as defined above does not populate the time periods (months) that do not have any data.  For example, if there is no activity in 2011.08, the measure formula will not report any data.  However, the user would like to see the aggregated value of all the activity that happened prior to this period.

I am not sure of how to change the formula to account for my requirements above, and any guidance is greatly appreciated.

Thanks once again Vadim.

Best regards,

Vijay.

[former_member186338]

Hi Vijay,

Starting from second question - it looks that after some time this formula will start working on empty base periods

For parent members you can try to use IF condition and some other formula. PERIODSTODATE function looks only at the current year, and does not cross over into other years - yes, this is correct. Please read documentation about MDX on the Microsoft site (not everything is supported by SAP, but most).

B.R. Vadim