Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
Hope you are doing well.
I need to compare that is this Customer in Company B also bought with Company A before in the last 2 months.
I can check whenever that customer ever bought with Company A, but I am stuck on last 2 months.
Please help, Thank you.
Company A Table
Date | Customer | Revenue |
01-Jan-24 | CA | 1,000.00 |
16-Jan-24 | CA | 2,000.00 |
01-Feb-24 | CA | 3,000.00 |
16-Feb-24 | CA | 4,000.00 |
17-Mar-24 | CA | 5,000.00 |
20-Apr-24 | CA | 6,000.00 |
01-Jan-24 | CB | 7,000.00 |
01-Feb-24 | CB | 8,000.00 |
16-Jan-24 | CC | 9,000.00 |
Company B Table
Date | Customer | Revenue |
10-Apr-24 | CA | 1,000.00 |
27-Apr-24 | CB | 2,000.00 |
30-Apr-24 | CC | 3,000.00 |
Result Table
Date | Customer | Revenue | No. Trasaction with Company A in last 2 months | Sum Revenue with Company A in last 2 months |
10 April 2024 | CA | 1,000.00 | 3 | 12,000.00 |
27 April 2024 | CB | 2,000.00 | 1 | 8,000.00 |
30 April 2024 | CC | 3,000.00 | 0 | - |
Solved! Go to Solution.
Hi @nutdiablo ,
Assuming there aren't any dimention tables that link Company A and Company B, the transaction count is the count of rows and the start of month begins on the first day of the month from two months before the current transaction date, try these:
Transactions within the last two months =
VAR __END_DATE =
SELECTEDVALUE ( TableB[Date] )
VAR __START_DATE =
EOMONTH ( EDATE ( __END_DATE, -3 ), 0 ) + 1
RETURN
CALCULATE (
COUNTROWS ( TableA ),
FILTER (
TableA,
TableA[Date] >= __START_DATE
&& TableA[Date] <= __END_DATE
&& TableA[Customer] = SELECTEDVALUE ( TableB[Customer] )
)
)
Revenue within the last two months =
VAR __END_DATE =
SELECTEDVALUE ( TableB[Date] )
VAR __START_DATE =
EOMONTH ( EDATE ( __END_DATE, -3 ), 0 ) + 1
RETURN
CALCULATE (
SUM ( TableA[Revenue] ),
FILTER (
TableA,
TableA[Date] >= __START_DATE
&& TableA[Date] <= __END_DATE
&& TableA[Customer] = SELECTEDVALUE ( TableB[Customer] )
)
)
Proud to be a Super User!
It is working great. Thank you very much.
Hi @nutdiablo ,
Assuming there aren't any dimention tables that link Company A and Company B, the transaction count is the count of rows and the start of month begins on the first day of the month from two months before the current transaction date, try these:
Transactions within the last two months =
VAR __END_DATE =
SELECTEDVALUE ( TableB[Date] )
VAR __START_DATE =
EOMONTH ( EDATE ( __END_DATE, -3 ), 0 ) + 1
RETURN
CALCULATE (
COUNTROWS ( TableA ),
FILTER (
TableA,
TableA[Date] >= __START_DATE
&& TableA[Date] <= __END_DATE
&& TableA[Customer] = SELECTEDVALUE ( TableB[Customer] )
)
)
Revenue within the last two months =
VAR __END_DATE =
SELECTEDVALUE ( TableB[Date] )
VAR __START_DATE =
EOMONTH ( EDATE ( __END_DATE, -3 ), 0 ) + 1
RETURN
CALCULATE (
SUM ( TableA[Revenue] ),
FILTER (
TableA,
TableA[Date] >= __START_DATE
&& TableA[Date] <= __END_DATE
&& TableA[Customer] = SELECTEDVALUE ( TableB[Customer] )
)
)
Proud to be a Super User!