Calculate Leads' Sales After First Contact

[razmochaev]

Hi everyone.

I'm quite new into DAX and I'm struggling with the following task.

I have two dimension tables -dimClients and dimDates, - and two fact tables: factSales and factCalls.

factCalls contains all the calls of potential leads, and factSales - all the sales.

I want to calculate all the sales that were generated by these leads after the date they called.

I use CALCULATE function (calculate (sum(factSales[SalesAmount])). I  suggest that I need to apply two filters on dim tables:

1. filter on dimDate table - to keep only dates after the date of call stored in factCalls;

2. filter on imClients table - to keep only those clientID that exist in factCalls table.

But I cannot come up with the right formula.

Any hint will be much appreciated.

[Greg_Deckler]

Still thinking through the second part. I believe the first part you could handle with something like:

CALCULATE(SUM(factSales[SalesAmount]),FILTER(factSales,factSales[date]>MAX(factCalls[date])))

[razmochaev · Accepted solution]

Hi, smoupre.

Thank you for the reply and for the hint you gave. I've come up with the solution that does the work:

LeadSales = CALCULATE([factSales];filter(dimClients;CONTAINS(factCalls;factCalls[ClientID];dimClients[ClientID]));filter(dimDates;dimDates[Date].[Date]>=MIN(factCalls[Date])))

I modified the formula you suggested as I needed only those sales, that occurred after the initial contact with the lead, i.e. starting from the date the lead appeared in the factCalls table.

And CONTAINS function does a great job filtering only those clients that appear in factCalls table.