Calculate number of cancellation between two dates

[Anonymous]

Hi All,

I have the following problem where I'd like to calculate total cancellations for each month depending on the cancellation date and debit date. Essentially, a customer signs up and agrees to a payment date where an amount is debited but sometimes some customers cancel within a particular debit month. How can I create a measure that looks at both cancellation and debit date, I've already created inactive relationships? 

Id	Sign_Up Date	Debit Date	Cancellation_Date
1	24/12/2021	15/01/2022	12/01/2022
2	10/12/2021	1/01/2022	20/12/2021
3	7/12/2021	1/01/2022	21/07/2022
4	8/12/2021	15/01/2022	16/05/2022
5	8/12/2021	15/01/2022	7/04/2022
6	8/12/2021	15/01/2022	8/12/2021
7	8/12/2021	15/01/2022	1/02/2022
8	13/12/2021	15/01/2022	15/08/2022
9	13/12/2021	15/01/2022	17/03/2022
10	14/12/2021	15/01/2022	24/01/2022
11	14/12/2021	15/01/2022	31/01/2022
12	14/12/2021	15/01/2022	14/12/2021
13	2/12/2021	1/01/2022	21/08/2022
14	12/11/2021	15/01/2022	30/08/2022
15	29/11/2021	1/01/2022	27/01/2022
16	7/12/2021	15/01/2022	13/07/2022
17	21/12/2021	15/01/2022	21/01/2022
18	21/12/2021	15/01/2022	26/04/2022
19	15/12/2021	15/01/2022	15/12/2021
20	17/12/2021	15/01/2022	5/07/2022

My expected result is looks at the Expected debit date; For example in Jan we had 9 who cancelled in Jan of 2022 including customers who cancelled in Dec 2021 as they were expected to make a payment in Jan of 2022. Of the 9 customers 5 cancelled before the expected debit date  

	Cancels	Cancel before  debit date
Jan	9	5
Feb	1	0
Mar	1	0
Apr	2	0
May	1	0
Jun	0	0
Jul	3	0
Aug	3	0

[Greg_Deckler · Accepted solution]

@Anonymous I did it like this. PBIX is attached. 

Cancels Before Debit Date = 
    VAR __DebitDate = MAXX(ALL('Table'[Debit Date ]),[Debit Date ])
    VAR __Cancellations = FILTER(ALL('Table'),[Cancellation_Date] < __DebitDate)
RETURN
    IF(MAX('Table'[Calcel Month]) = 1,COUNTROWS(__Cancellations) + 0,0)



Cancels = 
    VAR __Count = COUNTROWS('Table')
RETURN
    IF(MAX([Calcel Month]) = 1, __Count + COUNTROWS(FILTER(ALL('Table'),MONTH('Table'[Cancellation_Date]) = 12)),__Count)