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

[chris_eluva]

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.

[ryan_mayu · Accepted solution]

@chris_eluva 

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.

[chris_eluva]

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

[ryan_mayu]

@chris_eluva 

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