Forum Discussion

bbbbbiiiii's avatar
bbbbbiiiii
Helper II
5 years ago
Solved

Excel Formula into Dax

Hi all, I am trying to calculate Projected Attrtion Rate using the formula

Projected Attrition Rate = Cumulative Attrition Rate/# Cumulative Months in the Period * 12 months

i.e. : [(Jan+Feb+Mar)/3]*12 = [(0.61%+0.25%+0.8%)/3]*12

 

The Attrition Rate Overall  use in the table is:

DIVIDE(COUNT('Headcount'[Attrition Date]), DISTINCTCOUNT('Headcount '[Emp No.]))

 

Need some help to apply the formula into powerbi. Any help or reference? Thanks!

 

 

  • Mohammad_Refaei's avatar
    Mohammad_Refaei
    5 years ago

    Then you can iterate over a summarized calendar with your measure as follows:

    Attrition Rate =
CALCULATE (
    AVERAGEX (
        ADDCOLUMNS (
            SUMMARIZE ( 'Calendar', 'Calendar'[Date] ),
            "AttritionRate", [Attrition Rate]
        ),
        [AttritionRate]
    ),
    DATESYTD ( 'Calendar'[Date] )
) * 12

3 Replies

  • Mohammad_Refaei's avatar
    Mohammad_Refaei
    Solution Specialist
    5 years ago

    The way we can write the synatx could vary depending on your data structure. I assume that you have a calendar table.

    Attrition Rate =
CALCULATE (
    AVERAGEX ( 'Headcount', Headcount[Attrition Rate] ),
    DATESYTD ( 'Calendar'[Date] )
) * 12

     

    • bbbbbiiiii's avatar
      bbbbbiiiii
      Helper II
      5 years ago

      Thank you Mohammad_Refaei 

       

      However, my Attrition Rate is actually a measure. Which I cannot apply averagex on it. Any advice on this?

      • Mohammad_Refaei's avatar
        Mohammad_Refaei
        Solution Specialist
        5 years ago

        Then you can iterate over a summarized calendar with your measure as follows:

        Attrition Rate =
CALCULATE (
    AVERAGEX (
        ADDCOLUMNS (
            SUMMARIZE ( 'Calendar', 'Calendar'[Date] ),
            "AttritionRate", [Attrition Rate]
        ),
        [AttritionRate]
    ),
    DATESYTD ( 'Calendar'[Date] )
) * 12