Showing results for 
Search instead for 
Did you mean: 
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.
Super User
Super User

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



Helpful resources

Winner of T-Shirt Design

Power BI T-Shirt Design Challenge 2023

Find out who won the T-Shirt Design Challenge and the top 3 finalists.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.