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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Check if a contract has expired in a new measure (based on end date)

 

I'm trying to find out if a Contract has expired based of the Contract End date. (i.e Expiry = IF(endDate < Today(), 1,0)). I know how to do this by creating a new coloum but how do i do this using a measure? 

Also, how do i find a new customer in a measure?

Or a Lost customer (a new customer is somoene who's buying for the first time, Lost customer is where a contract has expired but not renewed Eg : Mark. John is an example of a renewed customer). 

The plan is to create a stacked bar chart with these measures, so they can be dynamic based of the time changes.
 

dataModel.JPG


Thank you.

1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

@Anonymous 

 

1. to calculate the expired contract number, you can try

Expiredcontract = CALCULATE(COUNTROWS(customer),FILTER(customer,'customer'[End Date]<today()))

 1.PNG

2. for new customer and renewed customer, I created a new table based on your original one.

Table = ADDCOLUMNS(SUMMARIZE(customer,customer[CustomerID],customer[Name],"contract number",COUNTROWS(customer),"maxexpirationdate",max(customer[End Date])),"ifrenew",if([contract number]>1&&[maxexpirationdate]>today(),"Yes","No"),"new customer",if([contract number]=1,"Yes","No"))

2.PNG

I am wondering your logic for new customer becuase the contract has already expired for Mark and Marie.

 

I am not sure if we can avoid to create a new table for your requests.

 

Let's see if someone else can have better solution for this.

 

Hope this is helpfu.





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

Proud to be a Super User!




View solution in original post

3 REPLIES 3
ryan_mayu
Super User
Super User

@Anonymous 

 

1. to calculate the expired contract number, you can try

Expiredcontract = CALCULATE(COUNTROWS(customer),FILTER(customer,'customer'[End Date]<today()))

 1.PNG

2. for new customer and renewed customer, I created a new table based on your original one.

Table = ADDCOLUMNS(SUMMARIZE(customer,customer[CustomerID],customer[Name],"contract number",COUNTROWS(customer),"maxexpirationdate",max(customer[End Date])),"ifrenew",if([contract number]>1&&[maxexpirationdate]>today(),"Yes","No"),"new customer",if([contract number]=1,"Yes","No"))

2.PNG

I am wondering your logic for new customer becuase the contract has already expired for Mark and Marie.

 

I am not sure if we can avoid to create a new table for your requests.

 

Let's see if someone else can have better solution for this.

 

Hope this is helpfu.





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

Proud to be a Super User!




Anonymous
Not applicable

@ryan_mayu Appreciate all your help, thank you. This has definetly put me in the right direction but its still wouldn't help me create a dynamic stacked chart, because the data wouldn't respond to time changes from a date slicer.

Is there any way of doing that?

 

 

@Anonymous 

 

Does that mean you want the result to be changed by a date slicer? Do you have a datetime table?





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

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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