How to Calculate Invoice before Churn

[shanipowerbi]

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

[Greg_Deckler · Accepted solution]

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

[shanipowerbi]

Thanks Problem solved Many thanks