Calculate churn rate - Except function

[rflipper]

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

[rflipper · Accepted solution]

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