Formula stops working when year filter is added

[jwin2424]

I am perplexed as to why this is happening. When I use the formula without adding a year filter, it works perfectly. When I filter to a year or specific years, it breaks. I don't understand what I am doing wrong 😞

What am I doing wrong?

The filter for the year is on the visual itself (Visual Level Filter).


***EDIT***

I have narrowed down the problem to one specific filter. For some reason, when this filter is applied, the numbers go haywire.

I have it exclude the blank values at the page-level filter. When excluding the blanks for the current year count, it is correct. But when I exclude the blanks using the PY measure, the number shrinks quite a bit. It does not do this when I apply any other filters. 

Here is the filter applied

Assigned to Partner =

LOOKUPVALUE(Accounts[name], Accounts[accountid], Leads[assigntopartnercompanyid])

Thanks

[parry2k]

@jwin2424 can you share your data model to understand it further.

[jwin2424]

The data model is a direct link to my company CRM, so I can't share the file. Here is my scheme (columns hidden)

Currently, the formula only references the table. The filters reference these tables:
Territory
Campaign
Leads

Where I am looking up the partner name is from the Accounts table. I already have a connection to the Accounts table through different primary key. I need it to remain that way. For that reason, I just did a lookup for the partner account. However, I have this issue not just on the partner filter, i can add a product filter and the issue is the same. 

So it seems to work with some filters, but not with all filters. If I just do current year, all the filters work. The second I ask it to give me the count from year - 1 it just breaks. I dont get it !

[Rupak_bi]

Hi @jwin2424 

modify your calculate statement as below and check.
calculate(count(leads,[LEA ID]), all(leads), created on year = __currentYR-1)

What I feel, you cannot refer to the previous year section in a matrix using measure, until and unless you open the table filter.

if this doesn't works, share a sample table with dummy data representing your "Leads" Table
 

[jwin2424 · Accepted solution]

Unfortunately, that doesn't work for me. It removes all filters and then won't apply page-level filters. This is the result

I did try a different varation of the formula. I used LOOKUPVALUE to retrieve the "created on" year. Since our fiscal calendar starts on Jan 1st and ends of Dec 31st, using the year can work by just using the actual calendar year and not the LOOKUPVALUE. For some reason, this formula worked. 

Leads (PY) =

VAR _PY =

MAX(Leads[Created On].[Year])-1

RETURN

CALCULATE(

    [Lead Count],

    Leads[Created On].[Year] = _PY

)

I am not sure why my lookupvalue year doesn't work.