Calculate all new customers with a second visit

[pbi_baller]

Hi,

I need to calculate the number of customers new in the previous month which have had 2 visits.

Fact table:

I also have a date dimension joined to the fact table which has an active relationship on visit date so TREATAS or USERELATIONSHIP may need to be used.

New customer is defined by their reg date. So the dax should count customers that registered and had two visits in the previous full month. 

From the above, only customer 1 should be counted, as customer 2 didn't have a registration date in the previous month.

Can anyone help?

[v-yangliu-msft]

Hi  @pbi_baller ,

Here are the steps you can follow：

1. Create measure.

Count =
var _table1=
SUMMARIZE(
    'Table',
    'Table'[Index],'Table'[Customer_ID],'Table'[Reg_date], 'Table'[Visits],'Table'[Visit date],
    "1",
    COUNTX(FILTER(ALL('Table'),
MONTH('Table'[Reg_date])=MONTH(TODAY())-1&&MONTH('Table'[Visit date])=MONTH(TODAY())-1&&'Table'[Customer_ID]=EARLIER('Table'[Customer_ID])),[Visit date])
)
var _table2=
ADDCOLUMNS(_table1,"2",
IF(
    [1]>=2,
CALCULATE(DISTINCTCOUNT('Table'[Customer_ID]),FILTER(ALL('Table'),'Table'[Customer_ID]=MAX('Table'[Customer_ID])&&[1]>=2))))
return
MAXX(_table2,[2])

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[v-yangliu-msft]

Hi  @pbi_baller ,

Here are the steps you can follow：

1. Create measure.

Measure =
var _lastmonth=MONTH(TODAY())-1
var _count=COUNTX(FILTER(ALL('Table'),
MONTH('Table'[Reg_date])=_lastmonth&&MONTH('Table'[Visit date])=_lastmonth&&'Table'[Customer_ID]=MAX('Table'[Customer_ID])),[Visit date])
return
IF(
    _count>=2,
CALCULATE(DISTINCTCOUNT([Customer_ID]),FILTER(ALL('Table'),'Table'[Customer_ID]=MAX('Table'[Customer_ID])&&_count>=2)))

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[pbi_baller]

I think I can use this if I can put it into a calculated column instead so it acts as a flag. Do I need to change the formula in any way since a calculated column will work on a row by row basis? 

Appreciate your help

[pbi_baller]

Hi Liu,

Thanks for the response! Measure works when shown in a table against a set of dates - like the screenshot you provided of it working. 

However, it shows blank as standalone in say a card visual. I need it to show a single figure for all customers not on a row by row basis. 

Would I need to amend the countx function?

[pbi_baller]

Bump

[pbi_baller]

@amitchandak @tamerj1 hopefully it's okay to tag - any ideas as top authors?

This is what I came up with but seems to show very low numbers

VAR _table = CALCULATETABLE(Fact_table, TREATAS(VALUES(dim.date[date]), Fact_table[reg_date]), previousmonth(LASTDATE(dim.date[date])) )

VAR _summary = FILTER( SUMMARIZE( _table, Fact_table[Customer_id], "c", COUNT(Fact_table[visits]) ), [c]>=2)

RETURN

COUNTROWS( _summary)