Forum Discussion
Anonymous
6 years agoNot applicable
YoY change in matrix table when there is data missing for certain months
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
2 Replies
- v-frfei-msft
Community Support
Hi Anonymous ,
ISINSCOPE should help you in your scenario.
Measure = IF(ISINSCOPE(Dimdate[Date].[Month]),[PY],BLANK())
- AnonymousNot applicable
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