Counting Unique Customers per Month Compared to Unique Customers Up to Each Month.

[CSMcVey]

I have the following tables in PowerPivot:

1. DateTable
2. MetricsTable
3. CustomersTable

The Metrics table is my 'fact' table and is linked to the DateTable and CustomersTable.

I would like to graph two values on a graph with the month on the x-axis (categories)

1. The number of unique customers, using integration, in the corresponding month
2. The number of unique customers, using integration, in all months up through corresponding month

I have the measure built for item #1: =CALCULATE(COUNTROWS(DISTINCT('CustomersTable'[CustomerID])), FILTER(MetricsTable, [IntegrationYesNo] = "Yes"))

What I can't figure out is how to do #2, I know I am close just can't get over the hump. I am limited to PP Version 1.

Thanks in advance.

 

[MD610]

I think I can get you what you want if I modify your first measure a bit.

Instead of counting distinct employees in the CustomerTable, can you count them in the MetricsTable using their CustomerID? Otherwise, your CustomerTable and DateTable probably aren't related in anyway and are both being used as Lookup Tables for the MetricsTable.

New Measure 1:
[UniqueCustomers]:= CALCUALTE(COUNTROWS(DISTINCT('Metrics'[CustomerID])), FILTER('MetricsTable', 'MetricsTable'[IntegrationYesNo] = "Yes"))

This should give you the same result as your original equation but now we can use it to build the second measure correctly.

Measure 2:
[UniqueCustomersRunningTotal]:=CALCULATE([UniqueCustomers],DATESBETWEEN('DateTable'[Date], blank(), LASTDATE('DateTable'[Date])), ALL('DateTable'))

I believe this should give you the number of unique customers up to the current month including all previous months.

 

[powerpivotpro]

See also this post:

New Customers Per Day Generalized to “New Customers per Month,” etc. « PowerPivotPro