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...
  • 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
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