Forum Discussion
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 tryConversion 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
Community Champion
Hi jwin2424
Please tryConversion 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
Resolver 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!