Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Anonymous
Not applicable

Calculate number of cancellation between two dates

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? 

IdSign_Up Date Debit Date Cancellation_Date
124/12/202115/01/202212/01/2022
210/12/20211/01/202220/12/2021
37/12/20211/01/202221/07/2022
48/12/202115/01/202216/05/2022
58/12/202115/01/20227/04/2022
68/12/202115/01/20228/12/2021
78/12/202115/01/20221/02/2022
813/12/202115/01/202215/08/2022
913/12/202115/01/202217/03/2022
1014/12/202115/01/202224/01/2022
1114/12/202115/01/202231/01/2022
1214/12/202115/01/202214/12/2021
132/12/20211/01/202221/08/2022
1412/11/202115/01/202230/08/2022
1529/11/20211/01/202227/01/2022
167/12/202115/01/202213/07/2022
1721/12/202115/01/202221/01/2022
1821/12/202115/01/202226/04/2022
1915/12/202115/01/202215/12/2021
2017/12/202115/01/20225/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  

 CancelsCancel before  debit date
Jan 95
Feb 10
Mar10
Apr20
May 10
Jun00
Jul30
Aug30



1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

1 REPLY 1
Greg_Deckler
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.