Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
mmpbi
Regular Visitor

Show this year and last year when slicer is on current year

I have an indicator (let's say profit as a percentage of revenue) of which I want to display (the current month) for curent year and the previous year in a barchart. However, I want to use a slicer to define what "the current year" is. If the slicer is set to 2019, I want to see 2 bars, one with 18 and one with 19. If the slicer is set to 16, I want to see 15 and 16 etc.

 

I managed to do this with 2 seperate measures in the value field of the barchart (the one for the previous year below), however, that results in the same legend/axis label for the year: it always only shows the one for the "sliced" year.

 

 

 

%Profit M-1Y = 
VAR YearMonth = 
Calculate(Max( DimDate[YearMonth] ), Month( DimDate[ActualDate] ) = Month( ToDay() ) ) - 100 Return
 
//DimDate[YearMonth is something like 202101 for january 2021
Calculate( Profit[%Profit], Filter( All( DimDate), Int(Value(DimDate[YearMonth])) = Int(YearMonth) ) )

 

(The measure for current year is identical, without the -100.)

 

Is there any way to do this just using only a single measure, overriding the slicer?

 

Thanks in advance!

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

Hi @mmpbi,

If you want to achieve a custom filter range that different from you operation on the slicer, you need to ignore the filter effect from slicer to your table.
Then you can write a measure to check current row contents and compare with extracted slicer selection items to return tag. After these steps, you can use this measure as a visual level filter to filter records.

Applying a measure filter in Power BI 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

Hi @mmpbi,

If you want to achieve a custom filter range that different from you operation on the slicer, you need to ignore the filter effect from slicer to your table.
Then you can write a measure to check current row contents and compare with extracted slicer selection items to return tag. After these steps, you can use this measure as a visual level filter to filter records.

Applying a measure filter in Power BI 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Ashish_Mathur
Super User
Super User

Hi,

See if you can get some help from my article here - Flex a Pivot Table to show data for x months ended a certain user defined month.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks for your reply, but I do not see how this solves my solution. It's about and excel and only remotely related.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.