Formula Containing Month Number

[Anonymous]

Hi All,

I'm stuck on a formula that I'm trying to work out, and I'm hoping the community can help me resolve this issue.

To start: I have a formula that utilizes the month number in which the data was captured in order to produce a percentage. This forula is called the Annualized Turnover Rate, and it looks like the below.

• (((# of Terminations in a Month)/(Month Number)*12)/(Average Headcount))

So, for example, let's say we have the following numbers:

• # of Terminations in July = 50
• Month Number = 7 (July)
• Average Headcount = 250

The formula using the above numbers would look like this:

• (((50)/(7)*12)/(250)) = 34.29% Annualized Turnover Rate

My issue is that I can't seem to create a formula that changes the month number (number 7 in the example) based on the month in which the Terminations and Average Headcount occurred. The data would look like this:

• January (1)
  • Terminations = 20
  • Average Headcount = 225
• February (2)
  • Terminations = 15
  • Average Headcount = 230
• March (3)
  • Terminations = 28
  • Average Headcount= 220

And what I need is a formula that makes those calculations and knows to insert the correct month number based on the month (1 for Jan, 2 for Feb, 3 for March, etc.). The formulas would end up looking like this:

• January
  • (((20)/(1)*12)/(225)) = 107%
• February
  • (((15)/(2)*12)/(230)) = 39.13%
• March
  • (((28)/(3)*12)/(220)) = 50.91%

What I want to do is plot this formula onto a line graph so we can see the trending annualized turnover rate month over month. I have pieces of the formula - I'm able to extract termination numbers and average headcounts from my data. The only piece I'm missing is the month number.

Any thoughts? I'm also happy to provide any additional context needed.

Thank you!

[Anonymous]

Hi @Anonymous ， PhilipTreacy， thank you for your prompt reply!

Based on your description, I have created a simple test for you, if it is not feasible for you, please feel free to reply. 

First, create a calculated column to extract the month number:

MonthNumber = MONTH('Table'[Date])

Then create annualised employee turnover metrics:

Annualized Turnover Rate = 
DIVIDE(
    (SUM('Table'[Terminations]) / MAX('Table'[MonthNumber]) * 12),
    AVERAGE('Table'[Average Headcount])
)

Next, we can aggregate data on a monthly basis and calculate annualised staff turnover rates:

Annualized Turnover Rate by Month = 
CALCULATE(
    DIVIDE(
        (SUM('Table'[Terminations]) / MAX('Table'[MonthNumber]) * 12),
        AVERAGE('Table'[Average Headcount])
    ),
    ALLEXCEPT('Table', 'Table'[MonthNumber])
)

Result for your reference:

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PhilipTreacy]

@Anonymous 

I could have a guess as to how your data looks but it would be much better if you could supply the data you are using.

You ask for a formula to make those calculations but how can I write such a formula without knowing what columns are in your data, what data types you are usinge etc?

Please provide sample data in a usable format (file or table) not an image.

Regards

Phil