Using Member Formulas, how do I pull a different time other than current member?

[kyle_tetschlag]

I am trying to create Member Formulas for Days Payable Outstanding (DPO) and Days Sales Outstanding. The monthly calculation I can figure out by the following:

[ACCOUNT].[ZBSAP] / [ACCOUNT].[ZFACOSSTD]*30

[ZBSAREXCL] / [ZFANETSALES] / 30

The full year is where I need help. To fill in the the below equation, I can't figure out how to get the sum of month's EB AP. We use a period 13 so I can't take the balance as of 2017.TOTAL. I have to either use YTD for 2017.12 or 2017.TOTAL - 2017.13.

((Sum of the month's Ending Balance Accounts Payable - Trade) / 12) / ([ACCOUNT].[ZFACOSSTD] / 360)

I tried the equation below but didn't work:

IIF([TIME].CURRENTMEMBER.PROPERTIES("LEVEL")="YEAR",((-([TIME].CURRENTMEMBER.ID,[ACCOUNT].[ZBSAP]-[TIME].CURRENTMEMBER.PROPERTIES("YEAR")&".13",[ACCOUNT].[ZBSAP])/12)/([TIME].CURRENTMEMBER.ID,[ACCOUNT].[ZFACOSSTD]/360)),(-[TIME].CURRENTMEMBER.ID,[ACCOUNT].[ZBSAP]/[TIME].CURRENTMEMBER.ID,[ACCOUNT].[ZFACOSSTD]*30))

[former_member186338]

Use correct syntax first of all!

Not:

CLOSINGPERIOD( [TIME].CURRENTMEMBER,[TIME].[LEVEL02])

But:

CLOSINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER)

First argument is level!!!

The level syntax depends on your system - on HANA it's different ("LEVEL 02" - with space)!

And use simple test to check very simple formula!

[former_member186338]

P.S. Read note: https://launchpad.support.sap.com/#/notes/0001958603

[kyle_tetschlag]

I put in a simple equation CLOSINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER),[ZFANETINCOME] and I am still getting an error when refreshing worksheet (see below). Is there something I need to update in our system? or a note to apply? When I try to save this member formula in development, I cannot process the dimension either.

One or more errors occurred.

The execution of report Default Report failed. Please contact your administrator.

Bad request.

Server message:

code: UJO_READ_EXCEPTION_018

severity: error

description: MDX statement error: An exception with the type CX_SY_CONVERSION_NO_NUMBER occurred, but was neither handled locally, nor declared in a RAISING

log id: uDjL2X4a7jYQcuGdb0tf1G

DATAVALUE:An exception with the type CX_SY_CONVERSION_NO_NUMBER occurred, but was neither handled locally, nor declared in a RAISING

MDX:

V1:An exception with the type CX_SY_CONVERSION_NO_NUM

V2:BER occurred, but was neither handled locally, nor

V3: declared in a RAISING

V4:

----------------- Framework message: The remote server returned an error: (400) Bad Request.

The remote server returned an error: (400) Bad Request.

[former_member186338]

P.S. It's a bad idea to reply after a long period...