Skip to Content
0
May 22, 2018 at 07:23 PM

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

221 Views Last edit May 22, 2018 at 07:25 PM 2 rev

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))