Skip to main content

Hello Pigment Community.

 

I am trying to calculate the commission that we have to pay to our partners and it is based on the first year of MRR that each new client signed through a lead from our partners has generated. That means, that if we sign a new client in 2022, on 2024 we will pay a commission for that client based on the MRR that it generated through its first year, in this case 2022 and not the MRR that it is generating now in 2024.  

At the moment I have this formula; 

This is showing me only the first 12 month of MRR for each client as you can see in the following screenshot:

 

 

The problem with this method is that if we have a client that starts in a different month than January for example November, we want that the MRR for December and November for that client next year to be the same as it was on the previous year. Therefore I am looking for a formula that allows me to Move only the first 12 months of MRR to the next years.

 

Than you in advance :)

Gabriel.

Hi Gabriel,

Have you tried something like this?

'Country Revenue'[BY LASTNONBLANK: Month.Year + 1][BY CONSTANT: Month.Year]

The first BY takes the last value of the year and shifts it to next year, and the second BY applies that Year’s amount on all the months within it.

It also enables you to get rid of the ISBLANK, which is not the best in terms of performance.

Hope this helps!
François

Good afternoon François, thank you for your quick reply. I have used the formula that you proposed and I did a little modification, as the MRR can vary from month to month, I first created another metric of the ARR by client to avoid having the last month of MRR apply to all the other months.  

Therefore, the formula currently looks like this:

I had to change Month.Year by Year as month was not a dimension of this metric. However, this results in the following:

As you can see the data has moved correctly, however the clients that didn’t have revenue on 2023 and that started on 2024 (not visible on the screenshot) have shifted to 2025, therefore when I get the total of 2024 to do the commission, I will not have the clients that were added in 2024 in their respective column. Is there a solution to this problem. Pretty much the objective is to move the data but without removing it from the last year, kind off like doing a copy and paste each year but automatically. As I don’t know if this can be achieved, I would appreciate some guidance towards this result or a similar one.

 

Thank you in advance,

Gabriel

IF(DAYS('CDN contract start date','Current date CDN')>365,IFBLANK('CDN CD MRR Final',PREVIOUS('01_Data Hub'::Month,12)),'Historic CDN CD')

This is the formula that ended up working for me in the case that someone needs to do something similar.

Reply


Ask your question here!

Terms & ConditionsCookie settings