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

2
votes

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.

1
daxpowerbi-desktop

1 Answers

0
votes

Your second approach looks reasonable. Try it without DISTINCT and FILTER.

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

The VALUES function returns a list of distinct values of its column argument that are within its filter context.