cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
PBIhelp1 Frequent Visitor
Frequent Visitor

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:
 
matrixissue.png
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 2
Community Support Team
Community Support Team

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

Hi @PBIhelp1 ,

 

ISINSCOPE should help you in your scenario.

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

Capture.PNG

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
PBIhelp1 Frequent Visitor
Frequent Visitor

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

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

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 253 members 2,302 guests
Please welcome our newest community members: