YoY change in matrix table when there is data missing for certain months

[Anonymous]

Hello,

I have financial data for several locations for both 2018 and 2019, there are certain locations that have missing data for certain months. I've created a few measures that combined show YoY change when there is both 2018 and 2019 data for that month, and display blank values when one or the other is missing. 

RPY = CALCULATE('Metric Select'[SelectedMetric],SAMEPERIODLASTYEAR(dimdate2[Date]))

DIFF = IF(CALCULATE('Metric Select'[SelectedMetric] - [RPY]) ='Metric Select'[SelectedMetric],BLANK(),CALCULATE('Metric Select'[SelectedMetric] - [RPY]))

YOY% = DIVIDE([DIFF],[RPY],BLANK())

This is what they look like in a matrix:

The issue I am having is that the aggregate % change of 24.84% for 2019, is taking into consideration Jan and Feb 2019 data when calculation % change. I do not want those included in the aggregation because we did not have jan and feb data for 2018, I only want to find aggregate % change for months where we had data for both 2018 and 2019. The row level calculation in the matrix is displaying correctly by showing blank % change values for Jan and Feb, but the aggregation is not filtering correctly.

Any help would be great

[v-frfei-msft]

Hi @Anonymous ,

ISINSCOPE should help you in your scenario.

Measure = IF(ISINSCOPE(Dimdate[Date].[Month]),[PY],BLANK())

[Anonymous]

Thanks for the reply! This seems to just produce a blank value for the aggregate? I'm trying to get the correct aggregate to display -- that is one that takes into account only months that have data for both years.

Thanks again, and let me know if you have any ideas