Forum Discussion
Ignore filter year selection
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? :)
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.
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)))