cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bbbbbiiiii
Helper II
Helper II

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!

 

bbbbbiiiii_0-1627703472592.png

 

1 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

View solution in original post

3 REPLIES 3
Mohammad_Refaei
Solution Specialist
Solution Specialist

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

 

Thank you @Mohammad_Refaei 

 

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

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

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors