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
Below is the example data - simplified