Hello Suzzy,

You can calculate a cumulative metric with a specific start date in Pigment using the following approach:

1. Define the start date for your cumulation.

You have selected January 1, 2023 as your desired start date for the cumulation.

You’ll use this date as a filter to ensure the cumulation begins only from this point onward.

2. Write the formula for the cumulation with the start date.

To cumulate values for a metric and ensure the start date is respected while applying a 3-month deduction, the formula logic would be as follows:

If the month dimension is greater than or equal to January 1, 2023, calculate the cumulative value of Metric1 across the month dimension, but select a 3-month offset. For any month before January 1, 2023, return no value.

This gives us:

IF('Month Dimension' >= DATE(2023, 1, 1), CUMULATE('Metric1', 'Month Dimension') nSELECT: 'Month Dimension' - 3], BLANK)

3. Explanation of the formula:

• The condition checks if the month dimension is greater than or equal to January 2023. This ensures the cumulation starts from the specified date. Any months prior to this will return no value.

• The cumulate function calculates cumulative values for Metric1 along the month dimension while applying a 3-month offset to adjust the range.

• For months before January 2023, no value is calculated, ensuring the cumulation respects the start date.

4. Dynamic adjustments:

If you want to make the start date dynamic (e.g., tied to a specific switchover date), you can replace the static January 1, 2023 date with a reference to a metric or dimension that dynamically holds the desired start date.

Please go ahead and raise a support ticket if you require further assistance or clarification regarding this.

I would be happy to connect with you to further discuss. 

Best regards,

Thank you for this ​@Khalid Awale