YTD Attrition %age

[Anonymous]

Hi - I have to create the attrition %age..

Already have date relation date and created measures for hires,month end HC and seperations 

The calculation for attr. %age is  - (Sum of exits/Average headcount) (*12/no of month)

Excel calualtion is below

Month	HC	Avergae HC	Sum of Exit	No of Month	Total Month	YTD Attrition %age
Oct	900	900	8	1	12	10.67%
Nov	930	915	10	2	12	11.80%
Dec	921	917	23	3	12	17.88%
Jan	870	905.25	1	4	12	13.92%
Feb	877	899.6 (Oct to Feb average)	50	5	12	24.54% = sum of exit i.e. 92/average hc i.e. 899.6 *12/5

My powerbi table have columns as 

Month end - Id- Hire Date- LWd

[Anonymous]

Average HC is not coming correct .. what can be the reason for that ?

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

First you need to create a dim_date table in your date model. Then you can use the following measure for average HC:

Measure =
IF (
    ISBLANK ( [HC] ),
    BLANK (),
    CALCULATE (
        [HC],
        FILTER ( ALL ( Dim_Date ), Dim_Date[Date] <= MAX ( Dim_Date[Date] ) )
    )
        / CALCULATE (
            DISTINCTCOUNT ( Dim_Date[Month] ),
            FILTER ( ALL ( Dim_Date ), Dim_Date[Date] <= MAX ( Dim_Date[Date] ) )
        )
)

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

Best Regards,

Dedmon Dai

[amitchandak]

@Anonymous , with date table measures like these can help

Avg HC  = AverageX(values(Date[Month Year]),[HC])

Average HC ytd = calculate([Avg HC], datesytd(Date[Date], "9/30"))

Sum of exit Ytd= calculate([Sum_of_exit], datesytd(Date[Date], "9/30"))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.