How can I find New Customers in the different months in powerbi?

[garluc93]

I'm creating a customer segmentation and I have this SAMPLE PBIX 

This is the output I would obtain 

I would see the number of customers in different months divided in new, gold, silver and bronze. I have created the measures to define gold,silver and bronze but I don't know how I could define new customer id dinamically in the months. I have tried to use the date of first order but I can only obtain the new customers at today. I need to see also their customer id.

totallast3months = 
var month3=CALCULATE(
SUM(Data[BOOLEAN]);
DATEADD(TIMELINKTABLE[Date];-2;MONTH))
var month2=CALCULATE(
SUM(Data[BOOLEAN]);
DATEADD(TIMELINKTABLE[Date];-1;MONTH))var month1=CALCULATE(SUM(Data[BOOLEAN]);
DATEADD(TIMELINKTABLE[Date];0;MONTH))

return
month1+month2+month3


MTotal by Segment = 
IF(
ISFILTERED(CustomerSegment[Segment]);
CALCULATE(
[Total];
FILTER(
Customers;
CALCULATE([totallast3months]) = SELECTEDVALUE(CustomerSegment[TotalLast3Months])
)
);
[Total]
)

[v-juanli-msft]

Hi @garluc93 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie

[v-juanli-msft]

Hi @garluc93 

Create a measure to calculate the numbers of dates for each customer in the last 3 months

(for example, if a customer has a date today-2019/11/27,

then this measure looks back to 3 months ago-2019/9/1~2019/11/27

to find if there is anther date record for this customer in this period.)

Measure =
CALCULATE (
    DISTINCTCOUNT ( data2[datalink] ),
    FILTER (
        ALLSELECTED ( data2 ),
        data2[Customer Number] = MAX ( data2[Customer Number] )
            && data2[datalink] <= MAX ( data2[datalink] )
            && data2[datalink] > EOMONTH ( MAX ( data2[datalink] ), -3 )
    )
)

Create a new table,

Then modify the measure as below

m-mark3 = 
var _tmp = ADDCOLUMNS(VALUES(data2[Customer Number]),"SL3MO",[sumlast3months_v2],"new",[Measure])
var _mk = SELECTEDVALUE('new table'[Mark])
return
SWITCH(
    TRUE(),
    _mk="Gold",COUNTROWS(FILTER(_tmp,[SL3MO] = 3)),
    _mk="Silver",COUNTROWS(FILTER(_tmp,[SL3MO] = 2)),
    _mk="Bronze",COUNTROWS(FILTER(_tmp,[SL3MO] = 1 )),
    _mk="notBuyer",COUNTROWS(FILTER(_tmp,[SL3MO] = 0 )),
    _mk="New",COUNTROWS(FILTER(_tmp,[new]=1)),blank())

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[garluc93]

Hi @v-juanli-msft , thank you for the answer. Do you think it's possible to use a measure that count the number of months before the record date indeed of -3? Or do you suggest to use a big number that cover the 3 years ( maybe using a bigger timeintelligence table)?

Can you explain me how the measure Measure function? In particular why 

data2[Customer Number]=MAX(data2[Customer Number]

[v-juanli-msft]

Hi @garluc93 

My measure

Measure =
CALCULATE (
    DISTINCTCOUNT ( data2[datalink] ),
    FILTER (
        ALLSELECTED ( data2 ),
        data2[Customer Number] = MAX ( data2[Customer Number] )
            && data2[datalink] <= MAX ( data2[datalink] )
            && data2[datalink] > EOMONTH ( MAX ( data2[datalink] ), -3 )
    )
)

data2[Customer Number]=MAX(data2[Customer Number]

means "group by "Customer Number", 

eg,  

count dates per customer =
CALCULATE (
    COUNT ( [dates] ),
    FILTER (
        ALLSELECTED ( table ),
        table[customer id] = MAX ( table[customer id] )
    )
)

EOMONTH ( MAX ( data2[datalink] ), -3 )

means,

eg,  data2[datalink]=2019/11/2, 

EOMONTH ( MAX ( data2[datalink] ), -3 )=2019/8/31 -the end of that month which "datelink"'s month substract 3 (11-3),

data2[datalink] > EOMONTH ( MAX ( data2[datalink] ), -3 )

means.

eg,

data2[datalink]=2019/11/2

we calcualte the dates after 2019/8/31, it is to say we calculate the dates from 2019/9/1~2019/11/2(month 9, month 10, month 11).

This is what i undertsand your "last 3 months", if i understand correctly, please let me know.

Do you think it's possible to use a measure that count the number of months before the record date indeed of -3? Or do you suggest to use a big number that cover the 3 years ( maybe using a bigger timeintelligence table)?

What do mean by the statement above?

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.