MTD Custom Measure

[former_member269849]

Hi Folks,

I want to create Month to date Custom Measure , I have in my Time dimension Days and I want to calculate  the accumulated value for each month. How Coud I achieve that?

Thank you.

FZ

[former_member186338 · Accepted solution]

Full structure of your TIME dimension with admin screenshot, please!

Vadim

[former_member269849 · Accepted solution]

Hi Vadim,

Thank's for help here is my Time dimension called "JOUR"

[former_member186338 · Accepted solution]

Then please provide text of your standard QTD measure!

Is it show correct results?

[former_member269849 · Accepted solution]

Yes it's showing correct reults.

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

[former_member186338 · Accepted solution]

Copy this text to the new custom measure with the name MTD.

Test MTD to be equal to QTD

Then edit MTD measure text replacing %TIMEQUARTERLEVEL% to something like %TIMEMONTHLEVEL%

Test

[former_member269849 · Accepted solution]

its giving me an error TIMEMONTHLEVEL dimension do not exist

[former_member186338 · Accepted solution]

Instead try directly LEVEL02 (or LEVEL03...)

P.S. Without %%!

[former_member269849 · Accepted solution]

Hi Vadim,

not giving any error , but it's not working.

best regards

[former_member186338 · Accepted solution]

Report results screenshots for all measures? LEVEL02. LEVEL03...

[former_member269849 · Accepted solution]

Hi Vadim,

Thank's in advance ,here are the screnshots you asked for:

Periodic

MTD

[former_member269849 · Accepted solution]

YTD

QTD

[former_member186338 · Accepted solution]

Sorry, but what is the measure formula used for MTD?

What is here:

SUM(PERIODSTODATE([%JOUR%].[????], [%JOUR%]...

Please show tests with LEVEL02 and LEVEL03...

[former_member269849 · Accepted solution]

I have in my hiearchy YEAR->Quarter->Month->DAY  do i did in the measure formula Level03 wich is Month , so for sceenshots before it's leveL03: SUM(PERIODSTODATE([%JOUR%].[LEVEL03]

YTD

QTD

Periodic

[former_member186338 · Accepted solution]

Use LEVEL02 - Month

Vadim

[former_member186338 · Accepted solution]

Sample with LEVEL02:

[former_member269849 · Accepted solution]

Still not working please could sho me the code you put in your custom measure ?

[former_member269849 · Accepted solution]

[former_member269849 · Accepted solution]

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

[former_member186338 · Accepted solution]

I suspect space before LEVEL:

Not:

[ LEVEL02]

But

[LEVEL02]

My working formula:

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

P.S. Just replace:

INACCT with TV_EXTRACTION

TIMEDAYS with JOUR

[former_member269849 · Accepted solution]

it's working now, thank you so much for your very kind help

FZ