Forum Discussion
Formula stops working when year filter is added
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])-1RETURNCALCULATE([Lead Count],Leads[Created On].[Year] = _PY)I am not sure why my lookupvalue year doesn't work.
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
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.
I am not sure why my lookupvalue year doesn't work.