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

Need a Subtotal that filters current YTD and Prior YTD

I am trying to measure the percent change from now compared to the prior year. Prior year is calculated using the SAMEPERIODLASTYEAR function which seems to prevent most solutions I'm finding online.

 

I want my subtotals in my matrix to ignore rows that aren't populated for both the current year and the prior year.

 

Here is sample data:

1 Data.png

 

Here are the results I get vs what I want:

2 Results.png

 

I feel like I must be missing some simple solution, something with SWITCH, FILTER, or SUMX or some combination of these... 

 

Please help!

2 REPLIES 2
Community Support Team
Community Support Team

Re: Need a Subtotal that filters current YTD and Prior YTD

@bueroj,

 

You may use ISINSCOPE to add a measure.

https://community.powerbi.com/t5/Desktop/Matrix-How-to-avoid-Total-and-Subtotal-aggregation-for-a-fi...

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

Re: Need a Subtotal that filters current YTD and Prior YTD

@v-chuncz-msft thanks for the reply. However I don't see how ISINSCOPE will help. It looks like that just removed subtotals for everything. I want to exclude select records from the subtotal (records that don't have data in BOTH year 1 and year 2). Am I missing how to use ISINSCOPE to identify records not in both years? I do want to have the subtotal displayed, I just want it to exclude particular records depending on if the record was there last year and this year.