Basis Points Calculation

[Anonymous]

I'm trying to write a formula, using variables, to calculate the basis points of an investment for the previous year.

The basic formula is (Rvenue/(Average Net Assets)/(Days in Period)/365*10000

The formula I"m using (in a variable) that seems to return a (not yet validated, but seems close) value is:

($(vFm_LastFY_YTD_Revenue))/($(vFm_LastFY_YTD_ANA))*3650000.0/$(vFm_DaysInLastFY_YTD)

(If I change the order of operations and put the 3650000 at the end, I get a '1' as the result, which I'm also not quite clear on. Why would order of ops matter here? Also, removing or adding additional parenthesis to control the order of operations seems to break the formula.)

All the values for the individual variables seem correct, and return positive numbers.

However, I've been asked to account for leap years, so I want to substitute the '365' with a variable. That variable (vFm_TotDaysInLastFY) is basically defined as YearEnd( Today(),-1 ,10)-YearStart( Today(),-1 ,10) and returns 365 for the last FY (again, correct).

However, when I try to substitute the variable vFm_TotDaysInLastFY into the original formula, I get a slightly different value from the original formula, and it is also negated:

($(vFm_LastFY_YTD_Revenue))/($(vFm_LastFY_YTD_ANA))*$(vFm_TotDaysInLastFY)*10000.0/$(vFm_DaysInLastFY_YTD)

Since all the individual values are positive, I don't see how I'm getting a negative number here.

I'm missing something, I'm just not exactly sure what....

Can anyone point me at what I'm doing wrong? Thanks!

[marcginqo]

Lee,

those formulas seem OK. And you are right, if you use it this way you must use the $ expanded. If it would be a constant you don't need to.

What is the definition of vFm_DaysInLastFY_YTD?

[Anonymous]

This one is a bit more complex.

vFm_DaysInLastFY_YTD:

(((Max({$<Asset_FiscalYear={'$(vLastFiscalYear)'},Asset_FiscalMonth={'$(vMaxFiscalMonth)'}>}[Asset_Date]))-(Min({$<Asset_FiscalYear={'$(vLastFiscalYear)'}>}[Asset_Date])))+1)

Asset_FiscalYear and Asset_FiscalMonth are evaluated in script as part of my Calendar, all based off of Asset_Date. vLastFiscalYear and vMaxFiscalMonth are also evaluated in the load script. All of these values, as well as the value for vFm_DaysInLastFY_YTD itself, all seem to show the correct values when I pop them into a measure on thier own. (vFm_DaysInLastFY_YTD was checked using DAYS function in Excel.)

[marcginqo]

Lee,

Try this:

($(vFm_LastFY_YTD_Revenue)) / ($(vFm_LastFY_YTD_ANA)) * ( ( $(vFm_TotDaysInLastFY) ) * 10000 )  /  ( $(vFm_DaysInLastFY_YTD) )

So put the last variable also in its own brackets.

[Anonymous]

Still no. Returns '-'. I think I tried some variation of that as well (putting all the variables in thier own parenthesis.)

Glad it's not just me.

This is the formula I'm using for the Current Year Value:

(($(vFm_CurFY_Revenue))/($(vFm_CurFY_YTD_ANA))/($(vFm_DaysInCurFY)))*365*10000

This one seems to work just fine, but if I swap the variables for last year's variables, it bombs.

Thanks for the help, but I'm calling it a day for now, and I have Jury Duty tomorrow. Hopefully come back to this on Thu.