Custom measure to calculate whole project value through time dimension

[Former Member]

Dear Experts,

I've a requirement from a customer, which seems to be relatively basic.

We have a Périodic Model to plan budget projects , in which we have one dimension called PROJECT . each project has a property "YEAR_CREATED" allowing to refer the creation date of the project. Ex. 2015.10.

Each year, the need is to calculate the whole value of each project from the creation date to the current member of the time dimension.

To answer this, I'm trying to add a new MEASURES FORMULAS "PTG" (Project To Go), put, I'm facing trouble and not sure to well understand what is possible to do.

Please find below the code i'm trying to work with :

MEMBER [MEASURES].[PTG] AS

IIF([%INDICATORS_CAPEX%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC",
SUM(PERIODSTODATE([%PROJECT%].[CREATED_YEAR], [%TIME%].CURRENTMEMBER),
-[MEASURES].[/CPMB/SDATA),

IIF([%INDICATORS_CAPEX%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP",
SUM(PERIODSTODATE([%PROJECT%].[CREATED_YEAR], [%TIME%].CURRENTMEMBER),
[MEASURES].[/CPMB/SDATA])

[former_member186338]

Please read my answers here: https://archive.sap.com/discussions/thread/3462987

Something like:

MEMBER [MEASURES].[PTG] AS IIF([%INDICATORS_CAPEX%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC",SUM([%TIME%].[2006.01]:[%TIME%].CURRENTMEMBER,-[MEASURES].[/CPMB/SDATA]),...

It will work on the reports with base TIME members! But parent time members are meaningless if you want to SUM all data!

[Former Member]

I also try this, but face to Mdx Statement Error :

MEMBER [MEASURES].[PTG] AS IIF([%INDICATORS_CAPEX%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC",SUM([%TIME%].[2015.01], [%TIME%].CURRENTMEMBER),-[MEASURES].[/CPMB/SDATA]),IIF([%INDICATORS_CAPEX%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP",SUM([%TIME%].[2015.01], [%TIME%].CURRENTMEMBER),[MEASURES].[/CPMB/SDATA]),IIF([%INDICATORS_CAPEX%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="AST",
([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL03])),IIF([%INDICATORS_CAPEX%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ",([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL03])),-[MEASURES].[/CPMB/SDATA]))));SOLVE_ORDER=3

[Former Member]

"I recommend you to create a measure that will sum everything starting from the first base member in the TIME dimension."

Good point Vadim 🙂 Thanks.

"PERIODSTODATE works only within single year"

What do you mean exactly ? is that mean that if I want to sum all base level from a current member time, i cannot use it ?

it this, what should be the correct MDX expression ?

I Try this but anything apears :

MEMBER [MEASURES].[PTG] AS IIF([%INDICATORS_CAPEX%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC",SUM(PERIODSTODATE([%TIME%].[2015.01], [%TIME%].CURRENTMEMBER),-[MEASURES].[/CPMB/SDATA]),IIF([%INDICATORS_CAPEX%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP",SUM(PERIODSTODATE([%TIME%].[2015.01], [%TIME%].CURRENTMEMBER),[MEASURES].[/CPMB/SDATA]),IIF([%INDICATORS_CAPEX%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="AST",
([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL03])),IIF([%INDICATORS_CAPEX%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ",([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL03])),-[MEASURES].[/CPMB/SDATA]))));SOLVE_ORDER=3

Tks for your help

[former_member186338]

Please read:

https://docs.microsoft.com/en-us/sql/mdx/periodstodate-mdx?view=sql-analysis-services-2017

[former_member186338]

Strange MDX 🙂

PERIODSTODATE([%PROJECT%].[CREATED_YEAR], [%TIME%].CURRENTMEMBER)

1. PERIODSTODATE works only within single year

2. [%PROJECT%].[CREATED_YEAR] - it's not a property value, but something meaningless...

Also why do you need to sum project starting from some period??? You will not have records earlier anyway!

I recommend you to create a measure that will sum everything starting from the first base member in the TIME dimension.