0 Replies Latest reply: Sep 29, 2017 4:33 PM by Mike Lutomski

# RangeSum of Unique Customer IDs where customers appear in multiple months

I'm trying to sum the unique count of customers in a 3 month range.  The customers can appear in more than one month.

I am getting the number I am looking for in a KPI by using the following:

Count( Distinct {<[Date] ={'>=\$(=AddMonths(Max([Date]),-2))<=\$(=Max([Date]))'}>} [Customer ID])

When I try to use the same calculation in a table and get the sum of the three months by doing the following:

RangeSum( Above( Count( Distinct {<[Date] ={'>=\$(=AddMonths(Max([Date]),-2))<=\$(=Max([Date]))'}>} [Customer ID]),0 ,3) )

I end up counting a Customer ID for each month it appears.

In the table below, for the count(distinct formula, I get 12, which is what I want

when I do the RangeSum(above, I get 14.  for 3/1/2017, I want the RangeSum(Above()) to equal 12.

I would like to see the following results in my table.

Date          Customer Count

1/1/2017    2

2/1/2017    3

3/1/2017    12

I tried using the sum(aggr before the rangesum(above, but still got the same results.

Any help is appreciated.  Thanks in advance.

 Customer ID Date 1 3/1/2017 2 3/1/2017 3 3/1/2017 4 3/1/2017 5 3/1/2017 6 3/1/2017 7 3/1/2017 8 3/1/2017 9 3/1/2017 10 3/1/2017 1 1/1/2017 11 1/1/2017 12 2/1/2017 1 2/1/2017