Forum Discussion
Multiple dates within 1 dataset
Hi guys,
I have a dataset that looks like this:
| Policy Number | Policy Start Date | Cancellation Date |
| 123 | 01/01/2019 | 05/05/2019 |
| 234 | 02/02/2019 | 06/06/2019 |
| 345 | 03/03/2019 | 06/06/2019 |
| 456 | 04/04/2019 | "" (Blank) |
I want to be able to create a measure that allows me to calculate a cancellation rate based on the above table.
Currently, I have the following formula:
Cancellation Rate = DIVIDE ( SUM ( Cancelled ) , CALCULATE ( COUNT ( Policy Number ) )
when Cancelled = IF ( ISBLANK( Cancellation Date ) , 0 , 1)
When I use the above measure, it gives me 100% conversion rate since it is only taking the policies that have been cancelled when I use the cancellation date as the x-axis.
I have tried
Cancellation Rate = DIVIDE ( SUM ( Cancelled ) , CALCULATE ( COUNT ( Policy Number ) , ALL ( [Policy Start Date]) )
but it doesn't give me the correct policy number.
Ideally, I want Policy Start Date as the x-axis and have the number of policies reflecting the Policy Start Date and also have the Cancelled Date count to sync / coincide with the Policy Start Date as a count. I want to see all the policies that were bought in January and the number of policies that were cancelled in the January also.
Anonymous ,
Use ALL(Table) instead of ALL(Table[Column]) as below:
Cancellation Rate = DIVIDE ( SUM ( [Cancelled] ) , CALCULATE ( COUNT ( 'Table'[Policy Number] ) , ALL ( 'Table') ))Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Anonymous ,
Use ALL(Table) instead of ALL(Table[Column]) as below:
Cancellation Rate = DIVIDE ( SUM ( [Cancelled] ) , CALCULATE ( COUNT ( 'Table'[Policy Number] ) , ALL ( 'Table') ))Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.