Multiple dates within 1 dataset

[Anonymous]

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. 

[v-yuta-msft · Accepted solution]

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