cancel
Showing results for
Did you mean:
Frequent Visitor

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

Thank you.

1 ACCEPTED SOLUTION
Super User II

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

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

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"))``

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!

3 REPLIES 3
Super User II

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

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

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"))``

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!

Frequent Visitor

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

Super User II

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!

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group