Chennai Power BI User Group

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Total marketable leads with multiple filters

Jump to Best Answer
  • 1.  Total marketable leads with multiple filters

    Posted 15 days ago
    Hi,

    I was hoping someone could assist in correcting my formula that didn't seem to work, please..

    I was trying to calculate Martetable leads (marketable leads = people who fall into the following criteria BUT  are CREATED in the last 2 years OR modified in the last 2 years)

    Marketable 2 years only =
    VAR marketable_leads = COUNTROWS(
    FILTER(
    'CRM New Lead',
    'CRM New Lead'[Send Marketing Materials] = "SEND" &&
    'CRM New Lead'[Student ID_Final]= "No" || BLANK() &&
    'CRM New Lead'[Latest UAC no.] = BLANK()
    )
    )
    VAR created_modified_2yr = FILTER('CRM New Lead',DATEADD('CRM New Lead'[Date_Created On],-2,YEAR) ||DATEADD('CRM New Lead'[Modified On].[Year],-2,YEAR))
    RETURN
    IF(created_modified_2yr,marketable_leads)

    the first part of the varibale works by itself (i have tested it and get some figure), I think the 2nd part of the formula that messed up. I was hoping someone could urgently assist to please...


    thank you so much.

    Regards
    Susan

    ------------------------------
    Susan Zhu
    Digital Marketing Analytics
    ------------------------------


  • 2.  RE: Total marketable leads with multiple filters
    Best Answer

    Bronze Contributor
    Posted 14 days ago
    Hi Susan,

    Please try this solution and let know if it works.

    Good luck,

    Marketable 2 years only =
    VAR marketable_leads =
     FILTER(
    'CRM New Lead',
    'CRM New Lead'[Send Marketing Materials] = "SEND" &&
    'CRM New Lead'[Student ID_Final]= "No" || BLANK() &&
    'CRM New Lead'[Latest UAC no.] = BLANK()
    )
    VAR created_modified_2yr =
    FILTER (
    marketable_leads,
    DATESBETWEEN ('CRM New Lead'[Date_Created On], MAX ( 'CRM New Lead'[Date_Created On] ), DATEADD ( 'CRM New Lead'[Date_Created On], -2, YEAR ))
    || DATESBETWEEN ('CRM New Lead'[Modified On], MAX ( 'CRM New Lead'[Modified On] ), DATEADD ('CRM New Lead'[Modified On], -2, YEAR)))

    RETURN
    COUNTROWS ( created_modified_2yr )

    ------------------------------
    Hoss Satour
    Data analyst
    Algiers - DZ
    numidiabi.wordpress.com
    ------------------------------



  • 3.  RE: Total marketable leads with multiple filters

    Posted 14 days ago
    Hi Hussein,
    Thank you.
    Your formula didn't return any error message at my end, however, I'm still not getting any result (not sure where it went wrong)??
    Thanks very much
    Susan

    ------------------------------
    Susan Zhu
    Digital Marketing Analytics
    ------------------------------