Forum Discussion

jwin2424's avatar
jwin2424
Icon for Resolver I rankResolver I
4 years ago
Solved

Filter a calculation based on custom year

I am trying to wrap my head around this, but I cannot get this right. I have custom dates, so time intellgence models don't work. Example, ONLY January 3rd - Jan 20th falls within January. Anytime I ...
Need Help
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi jwin2424 
    Please try

    Conversion Rate YoY =
VAR _LeadCount =
    CALCULATE (
        COUNT ( Leads[Lead ID] ),
        Leads[Qualification Group] = "Partner Qualification",
        NOT ( ISBLANK ( 'Company Leads Report'[Status] ) ),
        'Fiscal Calendar'[Year]
            = MAX ( 'Fiscal Calendar'[Year] ) - 1
    )
VAR _WonCount =
    CALCULATE (
        COUNT ( 'Leads'[Lead ID] ),
        'Company Leads Report'[Status] = "Closed - Won",
        Leads[Qualification Group] = "Partner Qualification",
        'Fiscal Calendar'[Year]
            = MAX ( 'Fiscal Calendar'[Year] ) - 1
    )
VAR _CurrentYR =
    DIVIDE ( _WonCount, _LeadCount )
RETURN
    _CurrentYR
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Hi jwin2424 
Please try

Conversion Rate YoY =
VAR _LeadCount =
    CALCULATE (
        COUNT ( Leads[Lead ID] ),
        Leads[Qualification Group] = "Partner Qualification",
        NOT ( ISBLANK ( 'Company Leads Report'[Status] ) ),
        'Fiscal Calendar'[Year]
            = MAX ( 'Fiscal Calendar'[Year] ) - 1
    )
VAR _WonCount =
    CALCULATE (
        COUNT ( 'Leads'[Lead ID] ),
        'Company Leads Report'[Status] = "Closed - Won",
        Leads[Qualification Group] = "Partner Qualification",
        'Fiscal Calendar'[Year]
            = MAX ( 'Fiscal Calendar'[Year] ) - 1
    )
VAR _CurrentYR =
    DIVIDE ( _WonCount, _LeadCount )
RETURN
    _CurrentYR
  • jwin2424's avatar
    jwin2424
    Icon for Resolver I rankResolver I
    4 years ago

    This worked. 

    I see MAX used a lot when determining this. I was wondering if maybe you could elaborate on why it was needed? 

     

    Thank you again!