Forum Discussion

jwin2424's avatar
jwin2424
Resolver I
1 year ago
Solved

Formula stops working when year filter is added

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

 



Need Help
  • jwin2424's avatar
    jwin2424
    1 year ago

    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. 

4 Replies

    • jwin2424's avatar
      jwin2424
      Resolver I
      1 year ago

      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's avatar
        Rupak_bi
        Super User
        1 year ago

        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