I have a situation like in a simplified data in the excel attached.

2 tables: Fact and Plan.

*Fact: showing how much Volume was sold to a customer, by each Saleman. The period in example is Month 1 and 2 , but actually this is from 2015-2016 (24 months).

Note that 1 customer can buy from multiple Salemen.

*Plan: showing which Customer is being assigned to call by which Saleman (SM). This is the current assignment (2017).

Note that 1 Customer can be assigned to multiple Salemen.

Some customer in the fact table (i.e. E, G, H no longer exists)

[u]Question: How to Calculate the: Number of Month (after a certain month, for example here is January) that a customer have volume >0?

(has to be a Calculated Column)

I tried the below formula - it works fine in the example simplified data, but it takes forever to calculate in my actual data (+1,000,000 rows) and froze my laptop. I am new , so I don't know what could be a better way to achieve this result.

Code: Select all

`=CALCULATE(DISTINCTCOUNT('Fact'[Month]),'Fact'[Customer]=EARLIER(Plan[Customer]),'Fact'[Volume]>0,'Fact'[Month]>1)`

Below is the example data - simplified

https://drive.google.com/file/d/0B7l_L5iVxXj-WE1Rem9SQk9VLTQ/view?usp=sharing