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.

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

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

      Hi,

       

      Try this

       

      =CALCULATE( AVERAGEX(Table, (power(2- [MembersRetained]/[Prev12Members],1/12)-1)), DATESBETWEEN ( Table[Date], EDATE(MIN(Table[Date]),-11),MAX(Table[Date])))

       

      I am assuming that Table is a calendar table.  there should be a relatioship from the the Date column of your base data to your Calendar table (Table).  In the slicer/Filter, select a certain month (which should be a column in your Calendar table)

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

        Hi Ashish_Mathur,

         

        Thanks for your reply! I tried your formula and apparently it is still not working (the moving 12-month average churn rate is not "moving") . Maybe it's better if I give you the formula I used for calculating the "MembersRetained" measure:

         

         

        MembersRetained = 

VAR membersInPrevious12Month = CALCULATETABLE(Table, SAMEPERIODLASTYEAR(Table[Date])) 

return 

CALCULATE(DISTINCTCOUNT(Table[Customer]), FILTER(Table,CONTAINS(membersInPrevious12Month, Table[Customer],Table[Customer])))

        I think the problem lies in the fact that our filter to get the 12-month range does not get into [MembersRetained] & [Prev12Members] at all. Thank you very much!

         

        Regards,

        Henry