Forum Discussion

bbbbbiiiii's avatar
bbbbbiiiii
Icon for Helper II rankHelper 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
Mohammad_Refaei's avatar
Mohammad_Refaei
Icon for Solution Specialist rankSolution Specialist
5 years ago

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's avatar
    bbbbbiiiii
    Icon for Helper II rankHelper 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
      Icon for Solution Specialist rankSolution 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