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

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

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.