I use the following formula to calculate the variance between my current forecast (2-FCST) and my original budget - IF(IsRevenue(),[2-FCST]-[BUD],[BUD]-[2-FCST]).
I’d like to update it so that is calculates the variance between my original budget and whatever forecast iteration I have selected within the page dimension. I believe this is possible by replacing [2-FCST] with language that points to the Version member selected in the page, but I don’t know how to write the formula. Is anyone familiar with how to write the formula?
Hi @brad.baker,
In your requirement, I assume you have your version (that holds individual forecast versions) in the pages section. If you have it set that way, and you want a variance column to show the difference between your budget version [BUD] and the forecast version selected in the pages section, then one way to do that is to create a calculated time perspective that does that for you.
The MDX formula for that would be
([Time Perspective].[Default].&[BASE],[Version].Default.&[BUD])
([Time Perspective].[Default].&[BASE],[Version].Default.CurrentMember)
Please feel free to share your feedback or let me know if you have any questions.
Hope this helps,
Navin
Thank you for your quick reply. Would I replace “2-Frcst” in my original formula by inputting the formula your provided as follows:
IF(IsRevenue(),[([Time Perspective].[Default].&[BASE],[Version].Default.&[BUD])]-[BUD],[BUD]-[([Time Perspective].[Default].&[BASE],[Version].Default.&[BUD])])
I tried doing this in model manager, but its telling me the syntax is wrong so I assume I’ve mis-keyed something.
thanks again -
Hey Brad, can you share where are you inputting this formula into? What dimension? and how is your report layout? Feel free to share screenshots, etc.
Of course. I’m trying to write the formula into a calculated Version in Model Manager (see screenshot). Currently I’m inputting the specific Version dimension into the formula - 2-FCST in this case - in order to calculate the variance between the forecasted figures and the budgeted figures. But I’d like to figure out how to replace the specific Version dimension with formula language that dynamically pulls the Version listed in the Page Members of a given Dataview or Template. In this scenario, if I had 3 - FCST selected in the Page Members, the formula would update to calculate the variance between 3-FCST and Budget. That way I don’t need to create multiple Variance versions each time I want to compare a new forecast version to budget.
