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