Complicated Calculated Formula Powerpivot! Help please!

[DPHugo]

hi Everyone,

It has been a while now that I am looking for an answer at this question. Maybe someone here can help me.

I am trying to compute conversion rate on consultation that my employees do to prospective clients.

I have a big table with all my invoices. One unique ID per invoices, a date creation, the name of the product billed, the revenue associated and the id profile of the client. Each of my clients have an ID (I have also a Clients table with the ID clients and all his personal info).
I do free consultation in order to close those prospective clients. For tracking purpose, I bill a 0$ invoice to each clients who book a consult. I would like to do a distinct count on id_profile who have both a consult and a productA billed.
My goal is to know for example for month of March, I have N number of consults booked whose X number that has been closed within (2 days; 30 days and so on) following the consultation date.

you will find a sample of my data attached,

Thanks in advance,

If you need more information to solve that problem, I wont be very far,


Hugo

https://docs.google.com/spreadsheets/d/1PLyKI8rg9rFLzseh23fd2uYPd8wM5vI84hV7QU07Ngw/edit?pli=1#gid=0

 

[DPHugo]

Looks like the file isn't public. I have requested access.

Weid. I think it is public now.

Thanks,

Hugo

 

[scottsen]

I'm confused on how you have NO tables and such in your Power Pivot model. Just some excel sheets/tables

Anyway, I imported the data, and sorta "guessed" at what might be useful. How about this?

=CALCULATE(COUNTROWS(Invoices),
    FILTER(ALL(Invoices), 
       Invoices[id_Profile_Invoiced] = EARLIER(Invoices[ID_profile_invoiced]) &&
       Invoices[Revenues] > 0 &&  
       EARLIER(Invoices[Revenues]) = 0 &&
       EARLIER(Invoices[Consultation]) = 1
   )
)

This will give you a number, in an CONSULT row, that represents the number of "FutureWins". I think from there you can get what you want?