Forum Discussion

rflipper's avatar
rflipper
Frequent Visitor
7 years ago
Solved

Calculate churn rate - Except function

Hello,    I have the following data structure:  Table: transactions Ref - Subscription reference  Date - Date of the order   I want to calculate some key metrics, for example, churn rate or co...
Need Help
  • rflipper's avatar
    rflipper
    7 years ago

    Looks like I figured out how to make it works: 

     

    NewSubscriptions = 
IF(
	ISFILTERED('transactions'[Date]);
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column.");
        VAR _CURRENT = CALCULATETABLE(DISTINCT(transactions[Ref]);FILTER(transactions;transactions[Date]))
	VAR _PREV = CALCULATETABLE(DISTINCT(transactions[Ref]);DATEADD('transactions'[Date].[Date]; -1; MONTH))
	RETURN
        COUNTROWS(EXCEPT(_CURRENT;_PREV))
)
    LostSubscriptions = 
IF(
	ISFILTERED('transactions'[Date]);
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column.");
        VAR _CURRENT = CALCULATETABLE(DISTINCT(transactions[Ref]);FILTER(transactions;transactions[Date]))
	VAR _PREV = CALCULATETABLE(DISTINCT(transactions[Ref]);DATEADD('transactions'[Date].[Date]; -1; MONTH))
	RETURN
        COUNTROWS(EXCEPT(_PREV;_CURRENT))
)
rflipper's avatar
rflipper
Frequent Visitor
7 years ago

Looks like I figured out how to make it works: 

 

NewSubscriptions = 
IF(
	ISFILTERED('transactions'[Date]);
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column.");
        VAR _CURRENT = CALCULATETABLE(DISTINCT(transactions[Ref]);FILTER(transactions;transactions[Date]))
	VAR _PREV = CALCULATETABLE(DISTINCT(transactions[Ref]);DATEADD('transactions'[Date].[Date]; -1; MONTH))
	RETURN
        COUNTROWS(EXCEPT(_CURRENT;_PREV))
)
LostSubscriptions = 
IF(
	ISFILTERED('transactions'[Date]);
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column.");
        VAR _CURRENT = CALCULATETABLE(DISTINCT(transactions[Ref]);FILTER(transactions;transactions[Date]))
	VAR _PREV = CALCULATETABLE(DISTINCT(transactions[Ref]);DATEADD('transactions'[Date].[Date]; -1; MONTH))
	RETURN
        COUNTROWS(EXCEPT(_PREV;_CURRENT))
)