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 count of new users. To achieve this, I need to get data for the previous period and compare it to the current period. In SQL I can do left/right join but in DAX I cannot figure out how to use Except function correctly. 

 

 

Looks like it works for new subscriptions, but if I cannot figure out how to change it to find lost subscriptions: 

 

 
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))
)
 
 
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))
)

1 Reply

  • 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))
)