Problem with Calculated Column

[henryvu93]

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
2/1/2015	A
2/1/2015	B
2/1/2015	D
2/1/2015	E
2/1/2015	F
2/1/2015	C
2/1/2015	I
2/1/2015	J
2/1/2015	K
2/1/2015	L
2/1/2015	M
2/1/2015	N
3/1/2015	A
3/1/2015	B
3/1/2015	C
3/1/2015	E
3/1/2015	D
3/1/2015	F
3/1/2015	G
3/1/2015	H
3/1/2015	I
3/1/2015	J
4/1/2015	A
4/1/2015	B
4/1/2015	C
4/1/2015	D
4/1/2015	E
4/1/2015	F

I wanted to create a calculated column with this formula:

"Prev12Members = CALCULATE(DISTINCTCOUNT(Table[Customer]), SAMEPERIODLASTYEAR(Table[Date]))" 

However, it does not result in any values. It worked when I tried creating a measure and created a table (Visualization) with Date & this measure. Just wonder if it is possible to create calculated column as mentioned? Any insight will be greatly appreciated! Thanks!

 Regards,

Henry

[Ashish_Mathur]

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]

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]

Hi,

Please describeyour entire question and show the expected result.

[henryvu93]

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]

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]

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

[Ashish_Mathur]

Hi,

Share the link from where i can download your file.  Please let me know exactly the numbers that you are expecting so that i can compare my answer with yours.

[henryvu93]

Hi @Ashish_Mathur,

I'm afraid I can't share with you the file as it's kind of confidential. However, you can take a look at the screenshot attached below:

As you can see, I just want the average of the last 12 months churn rate, which is the meaning of the "moving 12 months average churn rate" (for e.g. Dec 2017 measure will be average of churn rates from Jan 2017 to Dec 2017). The formula did not work so it showed blanks all over for the measure. Thanks!

Regards,

Henry

[Ashish_Mathur]

Hi,

I will need some data to work with.  Share a dummy dataset with Churn rate already computed there.

[henryvu93]

Hi @Ashish_Mathur.

Thanks. Please find the link below for your reference.

https://drive.google.com/open?id=14ba6G8fCo6E98m3Jh-6aOA7P7b6qL-eG

Regards,

Henry

[Ashish_Mathur · Accepted solution]

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]

Hi @Ashish_Mathur,

The formula worked perfectly! Thanks a lot for your help!

Regards,

Henry

[Ashish_Mathur]

You are welcome.