topic Re: Excel Formula into Dax in DAX Commands and Tips

Excel Formula into Dax

bbbbbiiiii — Sat, 31 Jul 2021 03:47:00 GMT

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!

Re: Excel Formula into Dax

Mohammad_Refaei — Sat, 31 Jul 2021 04:21:29 GMT

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

Re: Excel Formula into Dax

bbbbbiiiii — Sat, 31 Jul 2021 06:27:36 GMT

Thank you Mohammad_Refaei

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

Re: Excel Formula into Dax

Mohammad_Refaei — Sat, 31 Jul 2021 06:32:01 GMT

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