Subsetting Query in DAX

[AnthonyJ]

Hi,
I've looked around the internet and aren't able to see a comment on what I'm trying to achieve so I'm hoping someone will be able to to tell me if this is possible in a DAX measure.

By way of an example, I want to measure how many customers have a monthly subscription for the first 5 months but those same customers don't have a subscription on the 6th month.

The table would be transactional with a record of each month a Customer has been subscribed. Customers being a list ID's and subscriptionMonth would be an integer value with "0" representing the first subscription month.

Eg.

CustomerID, Subscription Start, Subsciption To , subscriptionMonth

12345, 1 Jul 2022, 31 July 2022, 0

12345, 1 Jul 2022, 31 Aug 2022, 1

12345, 1 Jul 2022, 30 Sept 2022, 2

etc...

In SQL it would look something like:

select count (distinct Customers)

from subscriptions

where subscriptionMonth <=5

and Customers NOT in (

          select distinct Customers,

           From subscriptions

           where subscriptionMonth = 6 )

Basically, I'd like the measure to create a list of Customers that are in the 0 to 5 months and then pass that Customer list through to the next level of the measure see if those Customers are in the list for the 6th month.

I'd like to do this dynamically in the front end because I want expand this out and parameterise the 0, 5 and 6 values so the end user could interact with a slider or similar and view the continuance rates for Customers in the 0 to 8 or 10 month range etc...

Thanks for your help and I hope this makes sense.

[AnthonyJ]

Hi,
I appreciate your time supplying the blogs on customer retention and the DAX code for measuring the 0-5 and 6 cohorts. This will, no doubt provide a solution to future features to build.

I've marked you solution as accepted as it seems PowerBI isn't able to dynamically create a list of customers and pass this through as a parameter to determine if those same customers were also in the sixth month or allow a variable slider to dynamically choose the range investigated. eg months 0-7 or 5-9.

Thanks

[AnthonyJ]

Hi @amitchandak,

Thank you so much for your reply. These will be great tools to have in the tool belt. You did find a hole in my analogy however as our processes allow for Customers to come in and out of the subscriptions so when comparing the 0 to 5 it has to be the same cohort of Customers being queried in the 6th. It is possible in our circumstances (however rare) that the 6th period could be the first month of subscription which would mean a Customer in the numerator that's not in the denominator. 

This may be a challenge but I'm interested to know if PowerBI can acheive this. Can PowerBI take a list of Customers found in the 0 to 5 and pass it through as an extra filter condition to the 6th in DAX?

The reason I want to do it in DAX is because I'd like a slider that will allow the variables (0 and 5) to move, allowing the end user to investigate different periods (For example: 0 to 7 and still there in 8th or 5 to 10 and still there in 11). It's because of this dynamic nature I'm trying to avoid pre-processing in PowerQuery.

Again, Thank you for your help and time.

Regards

Anthony

[amitchandak · Accepted solution]

@AnthonyJ , You just need two measure purchases in 5 months not in 6th ,

if based on rolling

refer

Power BI- Customer Purchasing in each of the last 3 months: https://youtu.be/bGtjoccEA38

same as lost customer here

Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...

Or use meausre like

Calculate(Sum(Table[Value]), filter('Date', month('Date'[Date]) <5 ))

Calculate(Sum(Table[Value]), filter('Date', month('Date'[Date]) = 6 ))

or use all

Calculate(Sum(Table[Value]), filter(all('Date'), month('Date'[Date]) <5 ))

Calculate(Sum(Table[Value]), filter(('Date') , month('Date'[Date]) = 6 ))

based on selected date or today

or use all

first 5 =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())

var _max = eomonth(_max1,(-1*MONTH(_max1)) +5 )
var _min = eomonth(_max1,-1*MONTH(_max1))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

6th  =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())

var _max = eomonth(_max1,(-1*MONTH(_max1)) +6 )
var _min = eomonth(_max1,(-1*MONTH(_max1)) +5 ) +1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

With any set of measure , follow lost customer logic give in blog and video