Excel Formula into Dax

[bbbbbiiiii]

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]

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]

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 · Accepted solution]

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