Making the Most of the Switchover Date

  • 10 January 2022
  • 2 replies

Userlevel 5
Badge +5


Chances are you’ve set up your calendar in your application and have grown curious about period types.

Period types can be very useful either as a filter (adding the Dimension along with Months helps isolate the actual from the forecasted data) or within formulas.

The ‘Switchover Date’ is a predefined Pigment Metric that is used to determine the point (or date) in which months are no longer actuals, but are forecast instead. The ‘Switchover Date’ will define your period type as “Actual” or “Forecast” based on this date. It represents the most recent date at which the data is updated. By default, this Metric has no formula, but rather is a manual input. Mostly, it’s easier this way, and you can update it every time you upload your data.



Automating makes data more reliable


A two step manual process is not as reliable as a calculated process - so you should consider calculating the ‘Switchover Date’. Most of the time, you’ll be basing the ‘Switchover Date’ over a particular set of data, which would be a Transaction List in Pigment. For this example, we’ll use a Sales list.


An example transaction list


Here, you can note that the data is not in a particular order.

The ‘Switchover Date’ can be calculated by taking the maximum of the Date property (or the date of the last transaction).

If your data has a date, you can use the MAX aggregator, which conveniently works with dates.


Sales.Date[REMOVE MAX: Sales]



However, if your raw data has no date but rather a Property based on the native calendar (e.g. Month), you may still use it by using the Properties of the Calendar Dimension. Here, we’ll use the Start Date Property of the Month Dimension.

Sales.Month.'Start Date'[REMOVE MAX: Sales]


However, in this example, the first day of the month is used rather than the actual transaction date.



Changing the behavior of the period type


If you don’t like how the period type is selected from the ‘Switchover Date’, feel free to adjust the formula!



By default, this is calculated in the formula of the Period type Property of the Month Dimension. You can adapt this to whatever is needed in your Application, of even add a second Period type Property based on another data set.



2 replies


Can you share why REMOVE is the aggregator you use here? Seems strange that you aren’t using BY or something else.


That said, this is a fantastic use of the data with the switchover functionality. Amazing other vendors don’t do this.

Userlevel 5
Badge +5

Hi Bill,

The BY modifier goes from a dimension to another, and in that case I’m going from one dimension / transaction (Sales) to none.


Here’s a small recap from our training material: