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 use time intellegence, it uses a calendar year. 

 

I have a calculation that results in a % value, and I simply want a formula that shows me the prior year's %. It seems like no matter HOW I filter, I keep getting the current year. Here is the last code I tried:

Conversion Rate YoY = 
VAR _LeadCount = Calculate(COUNT(Leads[Lead ID]), Leads[Qualification Group]="Partner Qualification", NOT(ISBLANK('Company Leads Report'[Status])))
VAR _WonCount = CALCULATE(COUNT('Leads'[Lead ID]),'Company Leads Report'[Status]="Closed - Won", Leads[Qualification Group]="Partner Qualification")
VAR _CurrentYR= DIVIDE(_WonCount, _LeadCount)

RETURN
    CALCULATE(
        _CurrentYR
        , 'Fiscal Calendar'[Year] = 'Fiscal Calendar'[Year] - 1
        )

 

Here is what it returns:

 

I have even tried other filter expressions, but it keeps giving me the current year. I don't know where I am going wrong. 

 

Thanks! 

  • 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

2 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion

    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

      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!