Solved

Tenure cohort calculation

  • 25 October 2023
  • 2 replies
  • 75 views

Userlevel 4
Badge +7

Hello,

I am trying to create a metric with the number of sales by seniority in month.

I have a metric with the number of new hires and a tenure cohort dimension made up of 0, 1 and 2+ months. For example if I have 2 new hires in September 2023, I want to see 2 in the “0” line in Sept-23, then 2 in the “1” line in Oct-23 and 2 in the “2+” line in Nov-23.

I hope this is clear enough! 

Thanks!

Elodie

icon

Best answer by Benoit 26 October 2023, 17:54

View original

2 replies

Userlevel 6
Badge +12

Hi Elodie,

I made a simpler reproduction, hoping the outcome is what you were trying to achieve:

here is my source metric of new hires per month

and here is the seniority by tenure month:

 

The formula I used is:

if('Tenure cohort'."0", 'Sales per month',

if('Tenure cohort'."1", 'Sales per month'[select: Month -1],

cumulate('Sales per month'[select:Month-2],Month) ) )

 

Hope it helps.

Best,

Userlevel 4
Badge +7

Hi Benoît,

Thank you for your help! I have adapted your formula to obtain the result I was looking for!

Have a nice day,

Elodie

Reply