Calculate month-wise attrition % with different formula for each month

[Anonymous]

Hi, 

I need to calculate and show month wise people attrition % (annualised) using below formula for each month - 

Attrition % for Jan = (Termination count for Jan / People count as in month of Jan) x 12

Attrition % for Feb = (Termination count for Jan + Termination count for Feb)/(Avg of people count for Jan and Feb) x 12 / 2

Attrition % for Mar = (Termination count for Jan + Termination count for Feb + term count for Mar)/(Avg of people count for Jan, Feb & Mar) x 12 / 3

and so on for each subsequent month.

How can I create a measure to do this or will there be any other way to do this??

Thanks in advance.

[kcantor]

@Anonymous 

You should have no problems transitioning your calculations to dax but we here in the forum will need more information in order to provide assistance.

Are your calculations showing actual column names? Do you have a date table? 

As an example, assuming you are showing actual column names, your measure could be modified into dax:

Attrition % for Jan = DIVIDE([Termination count for Jan], [People count as in month of Jan]) * 12

That calculation would assume you have the necessary measure. If your termination count rolls up, you would need to create the sum for that calculation and use the sum measure in place of "Termination Count for Jan" in the above sample measure. If you can provide sample data including cleansed information but using actual table and column names, we could provide help that could be more specific(aka copy paste measures with the dax explained). If you can share a file through dropbox it would be even easier to help you.

[Anonymous]

Wow. Great to see such quick responses. I am new in the Power BI world hence wasnt sure how its gonna work here. Sharing link to the file which has some sample data.

https://www.dropbox.com/s/77c625yfol2w47r/Sample%20data%20attrition.xlsx?dl=0

[vjnvinod]

@Anonymous 

can you show the sample data please?