Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

Unexpected behawior of measure when filtering

Hi All


I struggle with an issue, which happens when filtering my table.

I have a table with top 20 brands filtered and few KPI's.

This seems to be ok...

The issue starts when I try to filter out marketing_concepts with names starting from "nivea cream all"

I am sure, these marketing_concepts corresponds to only one brand - NIVEA so there are no such concepts under any other brand. I expect only values for Nivea brand should change but (I marked it with red) all values for SALES $ (PY) and

SALES $ (vs PY) change too...



here is a syntax for SALES $ (PY):

SALES $ (PY) =
    [SALES $],
    FILTER (
        ALL ( AVON_NIELSEN_DATABASE[year] ),
            = MAX ( AVON_NIELSEN_DATABASE[year] ) - 1

and a samples of data (it is fake, do not worry):


Pbix file:  link

Source table: link


My questions:

1) why does it happen?

2) how to make my measures working well in second scenraio?


Thanks in advance

Community Champion
Community Champion



Remove the year of Page Filter and Add a Slicer with Date of DateTable (Herarchy - Year)



Lima - Peru

View solution in original post

Community Champion
Community Champion



i make changes in your model:


Add a Calculated Column "DateREF" in your table:


-Dateref = DATE(AVON_NIELSEN_DATABASE[year],1,1)


Create a DateTable 




Related both tables (Date-DateRef)


SALES $ (PY) = CALCULATE([SALES $],DATEADD(DateTable[Date].[Date],-1,YEAR))


The Visual look like this:




Lima - Peru


Thanks. I have followed your instruction step by step and got blanks in the final measure:



My latest Pbix file: (Sales vs PY issue v3): link


Am I doing sth wrong?

Could you please explain why original filters do not work? Is it a bug? 

Now I feel unsure using Power BI filters



Community Champion
Community Champion



Remove the year of Page Filter and Add a Slicer with Date of DateTable (Herarchy - Year)



Lima - Peru



The error in your measure exactly don't know, but i recommend to use this functions or Time Intelligence Functions.


SamePeriodLastyear gives you in this case the same result.

Lima - Peru

@ Victor


It is working now. 

One more question. What if I do not want to use a slicer? May I do it different way?





In spite of there is a workaround for this issue I still consider this as a bug.

From final user perspective it may lead to serious misinterpretation data as it was in my case

@ PowerBI team 

Can we expect to get it fixed?



Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors