Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
@Anonymous
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.
Proud to be a Super User!
@Anonymous
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.
Proud to be a Super User!
@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?
Proud to be a Super User!
User | Count |
---|---|
128 | |
112 | |
99 | |
65 | |
62 |
User | Count |
---|---|
138 | |
116 | |
102 | |
70 | |
57 |