Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

Make matrix show data until current year of prior, and still be able to filter it by year.

Hello everyone, 

I am trying to have a matrix show data of current year or prior. First I tried to filter the data by the Year field through the filter pane like so: 


But apparently this is something that you can't do, so then I created a calculated column in my table as follows: 

Now this is fine, except for when a filter selection is made under the ANIO field. In that case, the matrix shows the selected years with thata, but also the years not seleced BLANK. I then tried to fix this by saying, OK, so I will make a filter condition so that the matrix only shows those AnioFiltr that are not null:


, but it does nothing, can anyone help me??
PD: the field used to filter the matrix is ANIO from a seperate calendar table:

As ou can see in the image, the ANIO in table "AÑO-MES" is the one field used in the filter, and the field AnioFiltr is from the table that has the data and is being displayed in the matrix.

Thanks in advance! 
Community Support
Community Support

Hi @eugeniaCastilla,

I'm not so clear with your description, did you mean the matrix shows the records until the current year period by default and it can be reset by filter effect to show corresponding records?

If that is the case, I'd like to suggest writing measure formulas with isfiltered function and applying on 'visual level filter' to filter records. (current power bi does not support creating dynamic calculated column/table based on filter effects because they are working on the different levels and you can't use child level to effect its parent)

Applying a measure filter in Power BI - SQLBI


Notice: the data level of power bi.

Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)


Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Memorable Member
Memorable Member

Hi @eugeniaCastilla , Have you an example of the actual report so that I can see what's happening or not happening?



Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors