Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
User | Count |
---|---|
103 | |
90 | |
78 | |
72 | |
68 |
User | Count |
---|---|
110 | |
96 | |
95 | |
74 | |
71 |