Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |