How to find unique count?

Occasional Visitor
I wanted to churn out unique individual visited my store broken down by month.

When I use pivot table, distinct count, it gives me the unique individual for that particular month only.

Would like to check how can I get distinct count for that month exclude those who visited in previous month?

For example, how to get the following outcome:
January
A
B
B
C
distinct count = 3

Feb (exclude A/B/C who came in Jan)
A
B
C
D
D
E
Distinct count = 2

Thanks!!
1 Reply

@Nicolejclew 

That could be done using DAX measures, result is

image.png

I guess you have dates in your source, not just month names. To work with dates in data model it's strongly recommended to use Calendar (aka Date table), otherwise things could be overcomplicated.

You may create it directly in Power Pivot, set relationship with your table

image.png

With that measure could be

New Distinct Visitors:=VAR prevVisitors =
    CALCULATETABLE (
        VALUES ( Table1[Visitor] ),
        PREVIOUSMONTH ( 'Calendar'[Date] )
    )
VAR thisMonthVisitors =
    VALUES ( Table1[Visitor] )
RETURN
    COUNTROWS (
        EXCEPT (
            thisMonthVisitors,
            prevVisitors
        )
    )

Creating PivotTable in rows use months from Calendar table, not from your one

image.png