Ignore filter year selection

[vani]

Hello everyone,

Iam trying to ignore the selection of an certain year.

I've the following measure:

Grow Year -1 = CALCULATE(SUM('Inscrições'[Value]);FILTER('Inscrições';and(not('Inscrições'[Pais]="Portugal");'Inscrições'[Ano]=max('Inscrições'[Ano])-1)))

I think that should work, bue when i change of year, the figure become empty (on this case table im using a table).

I hope you guys can help me !

Thanks !

[Greg_Deckler]

I'm not the best with Spanish/Portuguese but I'm thinking that your filter for year (Ano) being set to equal MAX of Ano is the problem here. So, I'm thinking that unless the Ano you are selecting is the MAX of Ano - 1 in your table, your measure is going to be blank.

[SqlJason]

As @Greg_Deckler said, if you just use Ano = max(ano -1), you will only see value for the year which is the max year - 1 and blank for everythign else. 

Which is why I have included the ALL(Ano) to override the context of the year. Now you should be able to select any year, and the measure should show year -1 of the selected year.

[vani]

I guys,

That doesn't solve my problem. 

When i select a year from the  slicer object , the table still become empty. 

There is a way when i select year from the slicer object and the values remain the same(check formula above)

Grow Year -1 = CALCULATE(SUM('Inscrições'[Value]);'Inscrições'[Year]=2014)

The problem is that the year is "hardcoded" , and when i use  'Inscrições'[Year] = max('Inscrições'[Year]) , a error is shown " A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

Thanks !

[SqlJason]

Did you try the formula I suggested?

Grow Year -1 = CALCULATE(SUM('Inscrições'[Value]);FILTER('Inscrições';not('Inscrições'[Pais]="Portugal"));FILTER(ALL('Inscrições'[Ano]); 'Inscrições'[Ano]=max('Inscrições'[Ano])-1)))

It should not give that error as you are using a filter function, and hence it should not give the error that you said. Can you try using my formula and then pasting the error?

Note how I used that formula in my model. You can see the test measure (where I used the formula I suggested to you) returning the previous year values.

 

It will also work if you use the Year in a filter.

[vani]

Sql Jason

I guess i didn't express as well as i expected. Your formula works , but when i change the year on the slicer object, the table becomes empty. What iam trying to get independently of the year selected , is that the table remains the same ( in other words, just ignore the filter year selected) ! 

With no year selected 

With year selected 

[SqlJason · Accepted solution]

Alright, I think I understood you now. What you want is to show the previous year no matter what year is selected. For eg, if we are on 2016, you always want to show 2015 data, even if we select 2010 or 2012 or any other year on filter.

1) Create a measure that will show the max year available in the fact table

MaxYear=CALCULATE(MAX('Fact'[Year]), ALL('Fact'[Year]))

2) Now make the measure as you want. Instead of hardcoding 2014, you can use the measure which will always return the max year available in fact, irrespective of the filter.

test=CALCULATE(SUM('Fact'[Sales]), FILTER(ALL('Fact'[Year]), 'Fact'[Year]=[MaxYear]-1))

Is this what you want? 🙂

[Tamerlane]

Kudos for this answer. Been stuck on this for almost a full work day (wanting to display previous year in line graph when current year is selected), this works great. A great alternative if SAMEPERIODLASTYEAR() is not working, which will happen very often when there are gaps in your data.

[vani]

Jason i will try it , and if it works i'll back on you. 

Thanks ! 

[SqlJason]

You know what, I just noticed this. Your Year column (Anos) in the filter clause comes from the fact table (Inscricoes). Ideally, it should come from a dimension tableand yuo should be using the year column from your dimension in your filter.

So in short, make a Ano table with a column for the year and then relate it to your fact table (Inscricoes) by the year. Then my formula should be changed to

= CALCULATE(SUM('Inscrições'[Value]);FILTER('Inscrições';not('Inscrições'[Pais]="Portugal"));FILTER(ALL('Ano'[Ano]); 'Ano'[Ano]=max('Ano'[Ano])-1)))

[SqlJason]

Can you try splitting your filter statements?

Grow Year -1 = CALCULATE(SUM('Inscrições'[Value]);FILTER('Inscrições';not('Inscrições'[Pais]="Portugal"));FILTER(ALL('Inscrições'[Ano]); 'Inscrições'[Ano]=max('Inscrições'[Ano])-1)))

Just check the syntax as I am not used to the Non-US form of separators 🙂