Month on Month YTD Attrition Percentage

[sureshcu]

Hi,

I need to display Month on Month Attrition percentage from Jan’17 to Aug’17 in clustered column chart. I am able to get Aug’17 month attrition % with the following formula.

% Annualized Attrition=Total YTD Attrition/Average of Headcount*(12/currentmonth)

I have written following measure to get current month number. The current month measure works fine for Aug’17.

Currentmonth = FORMAT(MAX(Append1[Reporting Month]),"MM") ‘which gives 08

But Current month is picking only 08 for all the months. How to get the current month number dynamically as below

for January month currentmonth should be 01

for February month currentmonth should be 02

for March month currentmonth should be 03

for April month currentmonth should be 04

for May month currentmonth should be 05

for June month currentmonth should be 06

for July month currentmonth should be 07

for August month currentmonth should be 08, It works fine as August is the current Month

[v-yulgu-msft]

Hi @sureshcu,

According to current description, it looks like the sample table structure doesn't match the formula you provided. So, I assumed that the source table structure should be:

If it isn't similar to your real table, please convert your table to above structure via unpivot, new calculated table or any other operation.

Based on above table, you should create some calculated columns.

YTD =
IF (
    Append1[HeadCount Month].[MonthNo] >= MONTH ( TODAY () ),
    0,
    CALCULATE (
        SUM ( Append1[Attribution Number] ),
        FILTER (
            Append1,
            Append1[HeadCount Month] <= EARLIER ( Append1[HeadCount Month] )
        )
    )
)

Average HeadCount =
CALCULATE (
    AVERAGE ( Append1[HeadCount] ),
    FILTER (
        Append1,
        Append1[HeadCount Month] <= EARLIER ( Append1[HeadCount Month] )
    )
)

% Annualized Attrition =
Append1[YTD] / Append1[Average HeadCount]
    * 12
    / Append1[HeadCount Month].[MonthNo]

Then, use a Matrix visual to display data.

Best regards,
Yuliana Gu

[sureshcu]

Hi,

Thanks for your effort. I have shared only summary which was consolidated in excel. I have two tables Headcount and attrition which was appended in Power BI. I have given source name HC for Heacount Table and ATTR for Attrition table. Entire data was uploaded in Power Bi from excel which is not summarized.  the following data is the example. i don't think Percentage can be achieved through calculated column. for example January Head coutn is 507. it will have 507 rows. Please help

[sureshcu]

Hi,

any thoughts?

[v-yulgu-msft · Accepted solution]

Hi @sureshcu,

You can first SUMMARIZE Heacount Table and Attrition tabletable so that you will get the table structure as I mentioned in above port.

For Example:

New Table=SUMMARIZE(HC , HC[Hiring Month] , "Total HeadCount", SUM(HC[Emp ID]))

Regards,
Yuliana Gu

[Greg_Deckler]

Can you post sample data? It would seem to me that if you created a table visualization and put your month name in it along with your measure that you would get the right answer per month as the context of the table row would constrain the calculation of Current Month.

[sureshcu]

Hi,

Here is the sample data