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 · Accepted solution]

Just tested on my system:

PL250 with member formula:

IIF([TIME].CURRENTMEMBER.PROPERTIES("LEVEL")="YEAR",(CLOSINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER),[PL110]),NULL)

Report result:

Tested on BPC without HANA ("LEVEL02").

Please show administration screenshot of your TIME dimension! Something is wrong here...

[kyle_tetschlag · Accepted solution]

I built off of your formula and worked through some other parts of the equation and got it to work like it is suppose to. Thank you for your help on this. Not sure what was throwing it off before but possibly missing parenthesis or did put in the IIF statement.

[former_member186338 · Accepted solution]

"missing parenthesis" - for sure!