Forum Discussion

henryvu93's avatar
henryvu93
Icon for Helper I rankHelper I
8 years ago
Solved

Problem with Calculated Column

Hi guys,   I have a table with the sample like this:    Date Customer 1/1/2015 A 1/1/2015 B 1/1/2015 C 1/1/2015 D 1/1/2015 E 1/1/2015 F 1/1/2015 G 1/1/2015 H ...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi,

     

    Try this formula

     

    =CALCULATE( AVERAGEX(VALUES(Customer[Date]), [Churn Rate]), DATESBETWEEN ( Customer[Date], EDATE(MIN(Customer[Date]),-11),MAX(Customer[Date])))

     

    Hope this helps.

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
8 years ago

Hi,

 

That will not work as a calculated column - it will only work as a measure.  But if the measure is already working for you, why do you want to write that as a calculated column?

  • henryvu93's avatar
    henryvu93
    Icon for Helper I rankHelper I
    8 years ago

    Hi Ashish_Mathur,

     

    Thanks for your reply. Actually after this step, I want to summarize the table into a new table with date & the measure. However, with the measure "Prev12Members", my table did not show anything. I am thinking if I can create a column and summarize the table based on that column it might be possible.

     

    Regards,

    Henry

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      8 years ago

      Hi,

       

      Please describeyour entire question and show the expected result.

      • henryvu93's avatar
        henryvu93
        Icon for Helper I rankHelper I
        8 years ago

        Hi Ashish_Mathur,

         

        Basically, with the source table, I want to have 2 measures in a particular month: "prev12Members" (customers in the last 12 months) & "MembersRetained" (customers who were there in the last 12 months & are still customers now). Then, I create a measure for customer churn rate, which depends on those 2. Finally, I want to create another measure, which is the moving (or rolling) 12-month average churn rate:

         

        Moving_Average_ChurnRate_12_Months = 

CALCULATE(
    AVERAGEX(Table, (power(2- [MembersRetained]/[Prev12Members],1/12)-1)),
        DATESBETWEEN (
        Table[Date],
        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE (Table[Date] ) ) ),
        LASTDATE (Table[Date] )
    ) 
)

         For the Table Visualization, I managed to get the churn rate, but when I tried to calculate the moving 12-month average churn rate, the formula did not work. Thank you very much!

         

        Regards,

        Henry