conversion rate calculation

Hi Anonymous ,

Is there any reason that your Lead table and the Deal table have to be in separate tables?  I would have imagined that the Lead table and your Deal table can be combined using a unique identifier key.  Is this assumption correct?  In that case, I would combine the Deal table and Lead table in one table and show the lead date and deal date side by side to enable the use of sumxing over the table for the duration of lead being converted into a deal having the calendar dimension table as a disconnected table for flexible duration analysis. I've created a dummy data to demonstrate the duration analysis that can be done.  From your descriptions I guess that your cut off dates for counting is calendar months instead of number of days which have elapsed, so for example, 5/30 and 6/1 are in the different month despite it is less than 30 days.  

To tackle a issue like this, for example, you can write a formula like below to produce the visualization as shown below which shows how many calendar month over total months to deal completion has elapsed since the lead was created for each of the given point in time.  

Which will give you an output like below (I repeat again, which shows how many calender months out of the time to deal completion has elapsed at any given point in time.)   

I attach an example pbix file.  The solution above may not be in line with your exact output requirement, but hopefully it will provide some idea about how to tackle a duration and time related analysis using a disconnected calendar table.    

Best regards,