Forum Discussion

shanipowerbi's avatar
shanipowerbi
Icon for Helper III rankHelper 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 ...
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]
  )

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity 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]
  )

 

shanipowerbi's avatar
shanipowerbi
Icon for Helper III rankHelper III
6 years ago

Thanks Problem solved Many thanks