Work out a sales conversion rate when the date of sale is different to the date of enquiry

[samgriffin]

Hi

Im a bit of a noob with Power BI but researched as much as i can to find a solution with no luck Was hoping someone with a bit more experience may be able to assist.

I am trying to display a conversion rate in a report. The formula is super simple Count of enquirytime (date of the enquiry) / count of deprecdate (date of the sale).

The issue i am having is that my report page users a date slicer split into month and year so i can see conversion rates for each month. I can either slice by  deprecdate (the date the sale was made) or by the date the enquiry was recieved (enquirytime). Both options obviously yield an incorrect answer.

What i need to do is display a count of all enquiries recevied in a given month and a count of all sales made in the same month and then divide the two answers

Is this possible?

[Anonymous · Accepted solution]

Hi  @samgriffin ,

Would you consider creating a new calendar table directly to slice through the calendar table's Year and Month?

Here are the steps you can follow：

1. Create calculated table.

Table 2 =
CALENDAR(
    DATE(
        2021,1,1),
        DATE(2022,12,31))

2. Create calculated column.

Year = YEAR('Table 2'[Date])

Month = MONTH('Table 2'[Date])

3. Create measure.

Measure =
var _selectyear=SELECTEDVALUE('Table 2'[Year])
var _selectmonth=SELECTEDVALUE('Table 2'[Month])
var _count1=
COUNTX(
    FILTER(ALL('Table'),
    YEAR('Table'[date of the sale])=_selectyear&&MONTH('Table'[date of the sale])=_selectmonth),[Index])
var _count2=
COUNTX(
    FILTER(ALL('Table'),
    YEAR('Table'[date of the enquiry])=_selectyear&&MONTH('Table'[date of the enquiry])=_selectmonth),[Index])
return
DIVIDE(
    _count1,_count2)

4. Result:

If I have misunderstand your meaning, please contact me and provide a pbix without privacy data.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly