topic Re: Filter a calculation based on custom year in DAX Commands and Tips

Filter a calculation based on custom year

jwin2424 — Fri, 29 Jul 2022 19:02:21 GMT

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!

Re: Filter a calculation based on custom year

tamerj1 — Fri, 29 Jul 2022 19:32:40 GMT

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

Re: Filter a calculation based on custom year

jwin2424 — Mon, 01 Aug 2022 18:27:56 GMT

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!