Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
dakins
Helper II
Helper II

Dax to find customers that have failed to renew subscription

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

1 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

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

View solution in original post

4 REPLIES 4
TheoC
Super User
Super User

@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

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

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.

 

 

 

 

 

@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

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

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!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.