Skip to content

Data Validation

By using the data validation option we can point the Phasing measure to the Phasing dimension we just created. This limits the available options to the specific elements from the Phasing dimension.

Select the highlighted cell and click on the Validation in the header. The data validation dialog will show up.

Data Validation Button

Then select Dimension: Phasing»Default against Validation and hit Save Validation

Data Validation Rule

From the Department Filter, choose Finance

Click on the highlighted cell and choose Even Phasing from the dropdown and enter -80000 as the value in the cell to the left against Rent.

Even Phasing

Now we can add our first Phasing rule to calculate even phasing across the Profit and Loss cube.

To begin, we will right-click on any of the months within the planning year and select Edit Formula.

We will restrict this to only the months within our default hierarchy, otherwise, the rule would apply to the annual element also which would cause a circular reference. We will also restrict it to the Budget Scenario. The formula is already restricted to the dollar measure, which is correct.

Click on Month - Don't Restrict and choose the Month - Restrict to the lowest elements in the Default Hierarchy, then Scenario - Don't Restrict to Scenario - Restrict to Budget

Then, enter the following formula:

IF(["Annual", "Phasing"]="Even Phasing", ["Annual"]/12, CONTINUE)
IF(["Annual", "Phasing"]="Even Phasing", ["Annual"]/12, CONTINUE)

Set the description to Even Phasing.

Formula Editor

Click Save Formula to apply the formula.

The formula added reads, if the annual phasing method is Even Phasing then take the Annual phasing amount and divide it evenly by 12, otherwise, continue. The continue function allows the cube to look for other rules which may also affect this particular cell.

As we click save, the formula takes effect immediately and we can see the amount, negative eighty-thousand is being evenly phased across the budget year.

Even Phasing Formula works.

If we change the amount to Phase, the budget will automatically update in real-time.