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

[eugeniaCastilla]

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: 

AnioFiltr = if(COSTES_MEDIOS_RADIO[ANIO]<=YEAR(TODAY()),COSTES_MEDIOS_RADIO[ANIO])

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! 

[v-shex-msft]

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

ISFILTERED, ISCROSSFILTERED – DAX Guide - 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)

Regards,

Xiaoxin Sheng

[davehus]

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

Thanks