Count unique values of filtered table

Question

I'd like to compute how many unique customerskeys I have within a certain date range.

This is the table I am working in:

Start         End    CustomersKeys
1-Jan-18    1-Feb-18    ?
1-Jan-18    1-Mar-18    ?
1-Jan-18    1-Apr-18    ?
1-Jan-18    1-May-18    ?

This is the table I need data from:

Date    CustomerKey
4-Feb-18    1
6-Feb-18    1
8-Apr-18    1
9-Apr-18    2
10-Apr-18   3

And this is what I want to end up with:

Start         End    Customers
1-Jan-18    1-Feb-18    0
1-Jan-18    1-Mar-18    1
1-Jan-18    1-Apr-18    1
1-Jan-18    1-May-18    3

I have tried a ton of different combinations of; COUNTROWS, FILTER, DISTINCTCOUNT, CALCULATE, DISTINCT, ALL, etc. But I keep running into errors. Advice is much appreciated.

Please check the third value in the Customers column in the result table. Shouldn't this be 1 in stead of 0? Or are the data in the Start column not correct? — Marco Vos
Oh you are right. I am sorry, and thanks for pointing that out! — Tom O

Marco Vos Marco Vos · Accepted Answer · 2018-10-17T11:19:38

Try someting like this as a New Column:

Customers =
CALCULATE (
    DISTINCTCOUNT ( 'data'[CustomerKey] ),
    FILTER (
        'data',
        'data'[Date] >= 'DateRanges'[Start].[Date]
            && 'data'[Date] < 'DateRanges'[End].[Date]
    )
)

Count unique values of filtered table

1 Answers