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...
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. 

parry2k's avatar
parry2k
Super User
1 year ago

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

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
     

    • jwin2424's avatar
      jwin2424
      Resolver I
      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.