cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
alevandenes
Helper III
Helper III

Static and changeable column in same histograph

Hi Community,

 

I am trying to figure out whether it is possible to have a static column and a "changeable" one in the same graph. 

To better explain i would for the column with the BLUE arrow to be static (always display data for the last 2 years) and for the column with the RED arrow to be changing based on a filter slicer (i could select for example this quarter or last quarter data).

alevandenes_0-1659526572473.png

do you know whether this is even possible?

Thanks in advance!

1 ACCEPTED SOLUTION
rbriga
Super User
Super User

Let's assume you are comparing duration, and you have a duration measure (the red arrow, dynamic).

You need to create a second, calendar -resistant measure:

Duration (Last 2 Years)=
VAR _MaxYear= 
CALCULATE(
 MAX('Calendar'[Year]),
 REMOVEFILTERS()
)
RETURN
CALCULATE(
 [Duration],
 REMOVEFILTERS('Calendar'),
 KEEPFILTERS('Calendar'[Year] => (_MaxYear-1))
)
-------------------------
Data analyst by day, hockey goalie by night.
Did I help? Then please hit that "kudos" or "accept as a solution" button!

View solution in original post

6 REPLIES 6
rbriga
Super User
Super User

Let's assume you are comparing duration, and you have a duration measure (the red arrow, dynamic).

You need to create a second, calendar -resistant measure:

Duration (Last 2 Years)=
VAR _MaxYear= 
CALCULATE(
 MAX('Calendar'[Year]),
 REMOVEFILTERS()
)
RETURN
CALCULATE(
 [Duration],
 REMOVEFILTERS('Calendar'),
 KEEPFILTERS('Calendar'[Year] => (_MaxYear-1))
)
-------------------------
Data analyst by day, hockey goalie by night.
Did I help? Then please hit that "kudos" or "accept as a solution" button!

somehow it doesnt exactly work for me.

does the first REMOVEFILTER formula have to be empty?

Okay, it works with the filled REMOVEFILTERS!! thanks a lot!

SpartaBI
Community Champion
Community Champion

@alevandenes so you need one measure that always show the last 2 years and one that is changeable. Or you can't use 2 measures and must put a label?

i think i can use two measures. just not sure how to 😕

@rbriga already gave you one and the other one is the just the simple aggregatiom that will be responsive to any filter context including from the date slicer

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.