Forum Discussion

shanipowerbi's avatar
shanipowerbi
Helper III
6 years ago
Solved

How to Calculate Invoice before Churn

Hi Experts,

 

I need little help, I want to calucate the invoice amount of the customer before churn. I have two tables which connected with unique user id.

 

User ID   Churn Date
12568      03 March 2020
45889      03 March 2020
12155      03 March 2020
12158      03 March 2020
21215      03 March 2020
21515      03 March 2020

2nd Table I have monthly Invoice table
Invoice Date    User ID    Invoive Amount
1 Jan 2020        12568       $100
1 Feb 2020        12568       $90
3 March 2020      12568       $80

In this case I need result for User ID "12568 = $90" means invoice before churn. Even his invoice generated in March 2020. We need to look only churn date and get the value before churn date

 

 

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    Perhaps:

     

    Before Churn Column in Table 1=
  VAR __UserID = [User ID]
  VAR __ChurnDate = [Churn Date]
  VAR __DateBeforeChurn =
    MAXX(
      FILTER(
        'Table2',
        'Table2'[User ID] = __UserID &&
          'Table2'[Invoice Date] < __ChurnDate
      ),
      [Invoice Date]
    )
RETURN
  MAXX(
    FILTER(
      'Table2',
      'Table2'[User ID] = __UserID && 
        'Table2'[Invoice Date] = __DateBeforeChurn
    ),
    [Invoice Amount]
  )

     

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Perhaps:

     

    Before Churn Column in Table 1=
  VAR __UserID = [User ID]
  VAR __ChurnDate = [Churn Date]
  VAR __DateBeforeChurn =
    MAXX(
      FILTER(
        'Table2',
        'Table2'[User ID] = __UserID &&
          'Table2'[Invoice Date] < __ChurnDate
      ),
      [Invoice Date]
    )
RETURN
  MAXX(
    FILTER(
      'Table2',
      'Table2'[User ID] = __UserID && 
        'Table2'[Invoice Date] = __DateBeforeChurn
    ),
    [Invoice Amount]
  )