Skip to main content

Hi Pigment Community,

I’m working on building a forecasting model and have encountered a challenge that I could use some help with.

The core of my model is based on using the "Month of Year" dimension to allow users to input information. However, I now need to incorporate the "Month" dimension for a specific year, which I want to select using a switchover date.

The issue arises when I create a new metric and add the "Month" dimension. After filtering for my designated year, the metric aggregates the total values across all columns, leading to an incorrect result. Essentially, the metric isn't giving me the precise data I need.

You might wonder why I didn’t implement the "Month" dimension from the start. My goal was to minimize the work required to maintain and update the model as we transition into a new year. In my current setup, the user inputs values for the "Month of Year," and I planned to assign these values to the desired year later, based on the switchover date.

My main question is: How can I add the "Month" dimension to my “FCST SQP BY SQP DATE - NBiz” metric without causing this aggregation issue? If there’s no known solution, I’m happy to provide more details about my model to see where changes could be made.

Thanks in advance for your help!

 

Metric with FCST information - I want to add month based on my switchover date so I can assign these values to a specific year

Below is an attempted solution, however it is not giving me the result I am looking for

This is what I tried to make in the formula playground, however you can see that the total value for the month in the above screenshot is shown in every column, when I want the values to be the same but under the “MMM YY” columns that come with the Month dimension

If you require any further information to understand my problem please let me know.

Thank you for the assistance!

Hi @darious ,

What I would suggest is creating a mapping metric which will map the “Month of Year”  to the correct “Month”. 

After you’ve created this metric you can use the BY with arrow (->) to “add”  the Month dimension into your new metric like this:

‘Source Metric’ /BY: → ‘Mapping metric’]

The arrow in the BY modifier ensures that you don’t replace the “Month of Year”  dimension by “Month”  but are keeping both dimensions in your target metric. 

Hope this helps you,
 

Hi @darious,

You should be able to solve your issue by using this formula :

'FCST SQP by SQP Date - NBiz'[BY:Month.'Month of Year']

It will remove the ‘Month of Year’ dimension and bring the Month using the property from the Month dimension, thanks to that all Januarys (Jan-23, Jan-24, Jan-25) will get only the value you entered in January in your input ‘FCST SQP by SQP Date - NBiz’ . You can then apply the filter based on your switchover date.

Regarding your solution, if you simply add the Month dimension to your input metric, you don’t have any link between Month and Month of Year, so all the Month of Year are aggregated for each Month.

Let me know if that solves your issue.

Hi again @darious ,

I just saw @Stef comment, maybe I didn’t understand it correctly and you want to keep both MoY and Month.

In that case, what @Stef suggested is the solution to go. Additionally, if January should go to Jan-22, Jan-23, Jan-24, February to Feb-22,Feb-23,Feb-24 etc. then shouldn’t need to create a mapping but leverage the MoY property from the Month dimension with the following formula :

'FCST SQP by SQP Date - NBiz' ADD:Month]DFILTER:Month.'Month of Year'='Month of Year']

 

Your input

 

Output with both Month and Month of Year assuming January should go to Jan-22, Jan-23, Jan-24, February to Feb-22,Feb-23,Feb-24 etc.

Hope this helps,

Thank you both!
At the moment I do not require MoY to continue my model so I will use @CDALMAY’s  solution.

Reply


Ask your question here!

Terms & ConditionsCookie settings