Dax to find customers that have failed to renew subscription

[dakins]

I need help writing a Dax for the scenario below for 15k customers

Inv_num	Cust_ID	Sub_package_name	Sub_purchase_date	Sub_75days_to_expir_notification_date	Sub_expiration_date
51822	1007	Basketball+	11/1/2020	07/18/2021	11/1/2021
51821	101	Football+	10/1/2021 (renewed)	07/18/2021	10/1/2022
51820	4562	Tennis+	1/5/2020	10/22/2020	1/5/2021
51819	101	Football+	1/1/2021	10/18/2021	1/1/2022
4000	1007	Basketball+	11/1/2019	07/18/2020	11/1/2020
51817	101	Football	1/1/2020	10/18/2020	1/1/2021

How do I write a Dax to show me a report for the following?
1. A unique list of Customer_IDs that have expiring subscriptions but have not renewed yet - sample Customer 1007
2. A unique list of Customer_IDs that have expired subscriptions and have left the business - sample customer 4562
3. A unique list of Customer_IDs that have not renewed their subscription by Sub_75days_to_expir_notification_date

Thank you

[TheoC]

@dakins, DAX won't generate you a report as such. If you have a table in Power BI structured in the way you do, it can flag the criteria you want. 

I'd recommend adding three calculated columns:

Check_1 = 

VAR _Expiring = IF ( TODAY () > 'tblFact'[Sub_75days_to_expiry] , "Expiring" , "Active" )

RETURN 

_Expiring

Check_2 = 

VAR _ExpDate = IF ( tblFact[End Date] >= TODAY () , "Expired" , "Active" )

RETURN 

_ExpDate

Check_3 = 

VAR _Sub75ExpDate = IF ( AND ( tblFact[Sub_75days_to_expiry] >= TODAY () , TODAY () <= tblFact[Sub_expiration_date] , "Not Yet Renewed" , "Active" )

RETURN 

_Sub75ExpDate

Just adjust the columns to match your field names entirely and same with the tables.

Hope this helps.
Theo

[dakins]

Hi and thanks Theo.

I have similar columns to check_1 and check_2 however, check_3 didn't work - I got an error "Too many arguments were passed to the AND function. The maximum argument count for the function is 2"

Also, since I have multiple records per customer that shows subscription history, how do I ensure the calculate column is scanning the entire table to return only customers that have expiring subscriptions but not customer that haven't renewed yet. Customer 1007 has 2 records,the calculated column will return invoice 4000 as expired and 51822 as active.

My desire result is a report that shows me customers with expiring subscriptions only.

[TheoC · Accepted solution]

@dakins, the Calculated Columns definitely work as I tested them. Make sure you have all the brackets etc in place. 

Also, the outputs are not based on Customer ID. They are based on the dates. So 1 customer has 10 subscriptions and only 3 subscriptions are expiring, it will only Flag the 3.

If you want to put a report together, use the Table or Matrix visual, drag all the columns you want in it. From there, just use the Visual's filter pane to show only expiring on the three columns that are doing the checks.

Let me know if you need more help.

Thanks mate.

Theo

[v-kelly-msft]

Hi  @dakins ,

For "A unique list of Customer_IDs that have expired subscriptions and have left the business",why it is customer  4562?

For " A unique list of Customer_IDs that have expiring subscriptions but have not renewed yet",will customer  4562 be considered?

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!