I want to do a formula using IF or many IF condition.
I am working with a block having months in columns and currencies in rows.
Explanation: My formula is suppose to say if at the level of dimension month we select let’s say February month, I am suppose to obtain in January and february each actual rate and for the rest of the months, having february rates. And I am suppose to have the same logic if I select any month.
Waiting for your response.
Thanks.
Page 1 / 1
Hello,
There is a mechanism we call switchover that you should first apply:
Start by creating a “Switchover Date” metric, type Date, with a version dimension or scenario.
Then create a “is Actuals” Metric type boolean with the simple formula:
if(Month.’End Date’>=’Switchover date’,TRUE)
This will give you TRUE when the month is in the “past” relative to the date, for each version/scenario.
Then you can easily do IF(‘is Actuals’, Actuals rate, Forecasted rate)
I’d suggest getting the standard apps where you’ll see how we typically deal with the FX conversion.
Once you have that, we can dig a bit more into your need: do you want the last actuals rate on all your past months and the same rate on all future months ?
Hello Nathan,
Thanks for your reply.
I tried to create a Switchover metric, but I am not sure of the result I should obtain after that.
I just have a table with month in columns and version in row.
For the Is actuals metric, the formula is not working.
I don’t know what can cause the problem. May be the Switchover Metric?
For Your last question, I want the actual month rates or the rates of the selected month to be applied on the rest of the YTG months
Thanks for your help.
Hi @Jessica T ,
It looks like you have a syntax error based on the first ‘ being in the wrong direction.
In regards to the second point you can make an adjustment to the first formula Nathan shared by using the PREVIOUS Function.
IF(‘is Actuals’, Actuals rate, PREVIOUS(MONTH)). This will take the last rate that you have actuals for and apply it to all future months.
