How to find unique count?

Question

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!!

sergeibaklan · Answer

Nicolejclew&nbsp;
That could be done using DAX measures, result is

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

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

Forum Discussion

How to find unique count?

1 Reply

Resources