YOY% quick measure shows additional year column on a year filtered matrix

[MaxItaly]

Hello everyone.

I noticed a strange behaviour while building a report.

I have created a matrix: customer number on rows, year on columns, and sales as value.

I have then added a quick measure: YOY% sales

[Code:

"Sales% YoY = 
IF(
ISFILTERED('Sales'[Date]);
ERROR("Le misure rapide della funzionalità di Business Intelligence per le gerarchie temporali possono essere raggruppate o filtrate solo in base alla colonna data primaria o alla gerarchia data fornita da Power BI.");
VAR __PREV_YEAR = CALCULATE(SUM('Sales'[Sales]); DATEADD('Sales'[Date].[Date]; -1; YEAR))
RETURN
DIVIDE(SUM('Sales'[Sales]) - __PREV_YEAR; __PREV_YEAR)
)"].

When this quick measure is added on the matrix, if I have a filter on a year, let's say 2013, the matrix will show 2014 too.

Is it possible to remove this behaviour?

(I'd upload the example pbix file, but I don't understand how to do.)

This is a problem for several reasons:

1. it shows an additional year that hasn't been selected;
2. the additional year shows always 100% as YOY value because the 2014 has not been selected;
3. the additional year shows no values for sales someone who isn't paying much attention may think that in 2014 we have had no sales (and it's terribly incorrect). 

In my original file, furthermore, I may select 2012 and 2014, and it would show me 2012, 2013, 2014, 2015, with 2013 and 2015 behaving as previously described, leading to reading misunderstandings.

Thanks.

[v-huizhn-msft]

Hi @MaxItaly,

When you select 2013 in the slicer, it should only display data of 2013. So you'd better upload the .pbix file and we can check where the problem come from.

You can upload the .pbix file as attachment(Highlighted in blue line), or you can upload your .pbix file to One Drive, and share the file to us, post the shared link(type your link the button highlighted in yellow background) here.



Best Regards,
Angelia

[MaxItaly]

Hi, many thanks @v-huizhn-msft.

I can't find the "Attachments" tab, so I used Zippyshare, I hope it's not a problem for you.

This is a replica of what I did without sensible data:   Pbix - Test.

I'd really like to hear your opinion about this: I spent a whole day trying to figure out how to solve this problem.

I tried different formula, but the problem persists...

-EDIT_1-
Would it be possible to hide the calculated measure when it is equal to "-100%"?
That would solve the problem, it's easy to do if it's a column, but I don't know if it's possible to do it with a measure.
I'm going to look for that...

-EDIT_2-

I tried using this formula:

"

Test =
IF(

(IF(
ISFILTERED('Sales'[Date]);
ERROR("Le misure rapide della funzionalità di Business Intelligence per le gerarchie temporali possono essere raggruppate o filtrate solo in base alla colonna data primaria o alla gerarchia data fornita da Power BI.");
VAR __PREV_YEAR = CALCULATE(SUM('Sales'[Sales]); DATEADD('Sales'[Date].[Date]; -1; YEAR))
RETURN
DIVIDE(SUM('Sales'[Sales]) - __PREV_YEAR; __PREV_YEAR)
)) <> -1;
IF(
ISFILTERED('Sales'[Date]);
ERROR("Le misure rapide della funzionalità di Business Intelligence per le gerarchie temporali possono essere raggruppate o filtrate solo in base alla colonna data primaria o alla gerarchia data fornita da Power BI.");
VAR __PREV_YEAR = CALCULATE(SUM('Sales'[Sales]); DATEADD('Sales'[Date].[Date]; -1; YEAR))
RETURN
DIVIDE(SUM('Sales'[Sales]) - __PREV_YEAR; __PREV_YEAR)
);
BLANK())"
This seems to work. I have to check if this may cause problems in other visual object, but it seems to be fine, atleast for the matrix.

-EDIT_3-

Using the screenshot as example, would it be possible to have YOY of 2013 when only 2013 is selected?
Maybe with some ALL() in the formula?