Project -to-Date

[Former Member]

I have requirement like

- project 1 starts on 2015.FEB, so my report has to show from February 2015 thru today.

- project 2 starts on 2009.JUL, so my report has to show from July 2009 thru today.

Can anybody please guide me how can I achieve it ?

[former_member186338 · Accepted solution]

Sorry, but no detailed info about dimensions, members, properties etc...

[Former Member · Accepted solution]

It's for Project Inception Date property of Project Dimension ...

[former_member186338 · Accepted solution]

Not clear without samples!

[Former Member · Accepted solution]

Using custom measures formula like YTD value ..I want Project -to-Date or Life - to -Date ...

[former_member186338 · Accepted solution]

Sorry, if you don't want to provide clear explanation with samples then I am unable to help.

[akos_beres · Accepted solution]

KAN,

Custom measure might not be the best way to go about this. You could probably do something on the reporting side. But as Vadim mentioned, you are not providing enough information for us to help.

What we need is:

- BPC version

- Brief business case

- Project dimension with property IDS

- Examples of data intersection where data is saved

- Future report layout

or

- Provide MDX example that you have tried to write

- Errors

Hope this helps

Akos

[Former Member · Accepted solution]

BPC version 10.0

Brief Business Case :

LTD Custom Measures for Account dimension.

Example :

If I am running my BPC report for July 2016 year and transaction data for the account contain the date for 2015 , 2014 & 2013 year then LTD measure filed will display the data from 2013 to 2016 current period year data.

While changing the context the report should display the value as discuss above .

I think its clear now .

I have not yet tried the MDX formula and never tired it before that's why looking for the help.

[former_member186338 · Accepted solution]

Please search the forum this question was discussed number of times:

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

Vadim

[Former Member · Accepted solution]

is tis code work for the requirement ?

MEMBER [MEASURES].[LTD] AS

    'IIF([%KONTO%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC",

        (SUM(PERIODSTODATE([%TID%].[LEVEL00], [%TID%].CURRENTMEMBER),-[MEASURES].[/CPMB/SDATA])+( SUM(OPENINGPERIOD([%TID%].[LEVEL00]):ANCESTOR([%TID%].CURRENTMEMBER,[%TID%].[LEVEL00]).PREVMEMBER, -[MEASURES].[/CPMB/SDATA]))) ,

     IIF([%KONTO%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP",

        (SUM(PERIODSTODATE([%TID%].[LEVEL00], c.CURRENTMEMBER),[MEASURES].[/CPMB/SDATA])+( SUM(OPENINGPERIOD([%TID%].[LEVEL00]):ANCESTOR([%TID%].CURRENTMEMBER,[%TID%].[LEVEL00]).PREVMEMBER, [MEASURES].[/CPMB/SDATA]))),

     IIF([%KONTO%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="AST",([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TID%].[LEVEL02])),

     IIF([%KONTO%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ",-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TID%].[LEVEL02])),-[MEASURES].[/CPMB/SDATA]))))';SOLVE_ORDER=3

[former_member186338 · Accepted solution]

Where do you get this code???

Please try my simple code in the previous post...

[Former Member · Accepted solution]

Hi Vadim ,

Found this code from one of the SCN thread .

The code you have suggested

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

Can this code calculate the previous years data also ??

[former_member186338 · Accepted solution]

Just test at least something yourself!

My code will calculate sum starting from 2006.01 - you can adjust it to sum earlier years... Just use the earliest TIME member you have in your system!

Vadim

[Former Member · Accepted solution]

ok got it .

Thanks Vadim . Its really helpful .

[former_member186338 · Accepted solution]

For sure the correct result will be only for base periods... and you have to add IIF to differentiate INC and EXP...

[Former Member · Accepted solution]

But below code which found in thread is doing the same right ?

correct me if I am wrong

MEMBER [MEASURES].[LTD] AS

    'IIF([%KONTO%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC",

        (SUM(PERIODSTODATE([%TID%].[LEVEL00], [%TID%].CURRENTMEMBER),-[MEASURES].[/CPMB/SDATA])+( SUM(OPENINGPERIOD([%TID%].[LEVEL00]):ANCESTOR([%TID%].CURRENTMEMBER,[%TID%].[LEVEL00]).PREVMEMBER, -[MEASURES].[/CPMB/SDATA]))) ,

     IIF([%KONTO%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP",

        (SUM(PERIODSTODATE([%TID%].[LEVEL00], c.CURRENTMEMBER),[MEASURES].[/CPMB/SDATA])+( SUM(OPENINGPERIOD([%TID%].[LEVEL00]):ANCESTOR([%TID%].CURRENTMEMBER,[%TID%].[LEVEL00]).PREVMEMBER, [MEASURES].[/CPMB/SDATA]))),

     IIF([%KONTO%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="AST",([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TID%].[LEVEL02])),

     IIF([%KONTO%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ",-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TID%].[LEVEL02])),-[MEASURES].[/CPMB/SDATA]))))';SOLVE_ORDER=3

[former_member186338 · Accepted solution]

Sorry, please stop copying some unknown code without understanding... Start coding yourself!

[Former Member · Accepted solution]

Based on your suggest develop and tested below code

but how to get previous all year data i mean dynamically system will identify like if system has data for previous 3 years then it should consider it

any different syntax to achieve the same

MEMBER [MEASURES].[LTD] AS

    'IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC",

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

     IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP",

              SUM([%TIME%].[2006.01]:[%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

[former_member186338 · Accepted solution]

Sorry, but what is not clear:

SUM([%TIME%].[2006.01]:[%TIME%].CURRENTMEMBER)

Will SUM all data from the earliest date in the system till the current period!

Start with the measure only for INC/EXP, remove code for BS accounts!

P.S. Test in the report and see results!