Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

    • Anonymous's avatar
      Anonymous
      Not 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