Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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]
)
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]
)
Thanks Problem solved Many thanks
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |