Dax for cancellation rates (daily, weekly, monthly)

[Anonymous]

Need help! I need to compare the cancellation rates for each day/week/month.  see my sample data below

1. Cumulative Active = previous cumulative active + new signup - cancel
2. Cancellation rate (day) = cancel (day) / cumulative active 
3. Cancellation rate (week) = cumulative cancel (as that week) / cumulative active 

Please find sample data below

https://we.tl/t-dBlnomWHUw

Left 3 columns are data we've got, right columns are datas we want to get from DAX. 

thanks!!!

[v-lili6-msft · Accepted solution]

hi, @Anonymous

After my research, you could try this way:

Step1:

Add a date table

Step2:

Use this formula to add a new table

Table = FILTER(CROSSJOIN(Table1,'Date'),'Date'[Date]>=Table1[New Active Date]&&'Date'[Date]<IF(Table1[Cancel Date]=BLANK(),MAX('Date'[Date]),Table1[Cancel Date]))

Step3:

Use your logic to create these measure

Result:

See more details, please try my pbix file.

Best Regards,

Lin