How to distinctly count customer IDs that visited a store in a given quarter only

[man2ma]

How to distinctly count customer IDs that visited a store in a given quarter only if the same customer ID visited in the previous quarter

I have a table

+-----------+----------+--------+-------+---------+
|Customer ID|Visit Date|Category|Product|Served by|
+-----------+----------+--------+-------+---------+
|1001       |03/17/2019|A       |P11    |Jone Doe |
|1003       |03/17/2019|D       |P12    |Jone Doe |
|1006       |03/15/2019|C       |P13    |Jone Doe |
|1009       |03/10/2019|G       |P14    |Jone Doe |
|1011       |12/12/2018|H       |P15    |Foo Bar  |
|1003       |11/11/2018|D       |P16    |Foo Bar  |
|1006       |09/10/2018|C       |P17    |Foo Bar  |
|1009       |10/10/2018|G       |P18    |Foo Bar  |
+-----------+----------+--------+-------+---------+

there are 4 customers but only 2 (1003 and 1009) visited in the previous quarter.

I used DATESINPERIOD but it counts all distinctly between the preceding quarters (I have a designated date table).

1st approach

customers_count = CALCULATE(DISTINCTCOUNT[Customer ID],DATESINPERIOD('Calendar'[Date],ENDOFQUARTER('Calendar'[Date]),-2,QUARTER))

2nd Approach

customers_count 2Q = 
VAR customers_count_1 = DISTINCT(FILTER(VALUES(Orders[Customer ID]))
VAR customers_count_2 = CALCULATETABLE(DISTINCT(FILTER(VALUES(Orders[Customer ID]))),
                                DATEADD('Calendar'[Date],-1,QUARTER))
RETURN
COUNTROWS(
          INTERSECT(customers_count_1 ,customers_count_2))

The expected count is 2 for the last quarter.

[v-cherch-msft · Accepted solution]

Hi @man2ma 

You may create a calendar table.Then you may get the count after adding a visual level filter.

Filter =
VAR Min_Index =
    CALCULATE (
        MIN ( 'Calendar'[Index] ),
        FILTER (
            ALL ( Customer ),
            Customer[Customer ID] = MAX ( Customer[Customer ID] )
        )
    )
VAR Max_Index =
    CALCULATE (
        MAX ( 'Calendar'[Index] ),
        FILTER (
            ALL ( Customer ),
            Customer[Customer ID] = MAX ( Customer[Customer ID] )
        )
    )
RETURN
    IF ( Max_Index - Min_Index = 1, 1 )

Count = DISTINCTCOUNT(Customer[Customer ID])

Regards,