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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. 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
TheoC
Community Champion
Community Champion

@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
Community Champion
Community Champion

@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.

 

 

 

 

 

TheoC
Community Champion
Community Champion

@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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.