Forum Discussion

kalpanaV's avatar
kalpanaV
Icon for Helper IV rankHelper IV
9 years ago
Solved

YTD Attrition

Hi 

 i want to calculate YTD attrition. Data is attached below

want to calculate YTD Attriton for voluntary count , involuntary count(both are measures  - Voluntary_Count, In Voluntary_Count).

Please help me on this.

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi kalpanaV,

     

    Based on your message, 'Capacity[Head_Count_MM]' seems not contains in current table, right?

    Maybe you can try to use below formula:

    % attr =
VAR currentIndex =
    MAX ( Attrition[Month_Key] )
VAR total =
    COUNTAX (
        FILTER (
            ALL ( Attrition ),
            Attrition[Month_Key] <= currentIndex
                && Attrition[Resignation Type] = "Voluntary"
                && Attrition[Competency] <> "SC"
        ),
        Attrition[Resignation Type]
    )
RETURN
    12
        * DIVIDE ( total, [Head_Count_MM] * currentIndex )

     

     

    In addition, it will be help if you share the pbix file to test.

     

    Regards,

    Xiaoxin Sheng

9 Replies

  • CahabaData's avatar
    CahabaData
    Icon for Memorable Member rankMemorable Member
    9 years ago

    Your post is not clear.  Please give example of what values you seek for %attr field.  ...and show the calculation.

    • kalpanaV's avatar
      kalpanaV
      Icon for Helper IV rankHelper IV
      9 years ago

      This is the output.

      I want to calculate %ytd attrition. 

      formula i have tried is % attr =
      var currentIndex= Attrition[Month_Key]
      var currentHC= Capacity[Head_Count_MM]
      var total = CALCULATE(COUNTA(Attrition[Resignation Type]),Attrition[Resignation Type]="Voluntary",Attrition[Competency]<> "SC",FILTER(Attrition, Attrition[Month_Key]<=currentIndex))
      return 12* DIVIDE(total,Capacity[Head_Count_MM]*currentIndex)

      but this measure is displaying null values.

      • CahabaData's avatar
        CahabaData
        Icon for Memorable Member rankMemorable Member
        9 years ago

        I'm not ready for dax yet - - still trying to figure out the plain old math

         

        what is the raw data and what is calculated?  I don't understand how those % values are being calculated, nor HC for that matter....