how many customer keep trading next 3 month after create accounts date.

[Anonymous]

Dear All,

I would like to analyze how many customer keep trading next 3 month after create accounts.

I try to create some measure but i can't input into Chart

Table 1 _  Date

Table 2 _ Customer

Customer ID	Account Number	Create Date
AAA	1	01-01-2018
AAA	2	02-01-2018
AAA	3	03-01-2018
BBB	4	04-02-2018
BBB	5	05-02-2018
BBB	6	06-02-2018
CCC	7	07-03-2018
DDD	8	07-03-2018
EEE	9	09-04-2018
EEE	10	10-04-2018

Table 3 _ Transaction

Account Number	Bill Number	Total Transaction	Transaction Date	Transaction YYYYMM
1	123	300	01-01-2018	201801
1	124	300	01-02-2018	201802
2	125	300	01-02-2018	201802
2	126	300	01-02-2018	201802
4	127	300	07-02-2018	201802
5	128	300	07-03-2018	201803
6	129	300	07-04-2018	201804
7	130	300	07-03-2018	201803
1	131	300	01-04-2018	201804
1	132	300	01-05-2018	201805

Result expect

Customer ID	1st create account  month	Trading in next 3 month count	Note
AAA	2018-Jan	2	Trade in Jan,Feb, Apr, May
BBB	2018-Feb	3	Trade in Feb,Mar,Apr
CCC	2018-Mar	1	Trade in Mar
DDD	2018-Mar	0	No Trade
EEE	2018-Apr	0	No Trade

1st create account  month  = CALCULATE(
MIN(Customer[Create Date]),
FILTER(Customer,Customer[AAA]=EARLIER(Customer[AAA]))
)

Trading in next 3 month count = CALCULATE (
DISTINCTCOUNT(Transaction Date[Transaction YYYYMM]),
DATESINPERIOD ( DimDate[Date],MIN(DimDate[Date]),3, MONTH )
)

When i move "Trading in next 3 month count" into legend - chart , it don't allow me .

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

As we know we cannot add the measure to the legend. So we can create a calculated table to work around, and create visual using the table.

Table = 
SUMMARIZECOLUMNS (
    Customer[Customer ID],
    "1st create account  month", 'Customer'[1st create account  month],
    "Trading in next 3 month coun", [Trading in next 3 month count],
    "Note", IF (
        ISBLANK ( 'Customer'[Trading in next 3 month count] ),
        BLANK (),
        "Trade in "
            & CONCATENATEX ( DISTINCT ( 'Transaction'[Month] ), 'Transaction'[Month] )
    )
)

[Anonymous]

Thank for your support, your solution is working with my situation