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
– Marco Vos, Commented Oct 17, 2018 at 10:10
Oh you are right. I am sorry, and thanks for pointing that out! — Tom O
– Tom O, Commented Oct 17, 2018 at 10:20

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

4

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]
    )
)

answered Oct 17, 2018 at 11:19

Marco Vos

2,9771 gold badge11 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tom O Over a year ago

Had to remove the ".[Date]", due to relationship issues. Other than that it worked perfectly. Thank you very much!

Collectives™ on Stack Overflow

Count unique values of filtered table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related