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

Compare value with selected value

Hi ! 

 

I need to display data on a histogram for the last 3 years, knowing that the user has selected the max year.

I have a dataset "FACTS" showing sales by product for each year.

QLB_1-1669207889151.png

 

I also have a table "DIMENSION" , with only selectable years. This table is linked to the previous one by the YEAR variable, but the relationship is inactive.

QLB_2-1669207922064.png


I made a segment allowing a simple selection of a year in a drop-down list.

QLB_3-1669207960443.png

I have a measure, calculating the sum of sales.

 

I have a histogram displaying this measure by year.

QLB_4-1669207997115.png

I want to add a filter to this visual to show only the last 3 years. For example, if the user chose 2020, we should see 2020, 2019 and 2018. As a result, the data must be displayed if and only if : YEAR - SELECTEDVALUE in (0,1,2)

But I don't know how to write this in DAX.

 

Can anyone help me?

Thanks

1 ACCEPTED SOLUTION
QLB
Frequent Visitor

Hi @AilleryO ,

Thank you for your help, unfortunately it didn't work. Finally, I worked around the problem by using an additional table linking the year to be selected to the years to be viewed. 

QLB_0-1669728195417.png

Regards, 
QLB

View solution in original post

2 REPLIES 2
QLB
Frequent Visitor

Hi @AilleryO ,

Thank you for your help, unfortunately it didn't work. Finally, I worked around the problem by using an additional table linking the year to be selected to the years to be viewed. 

QLB_0-1669728195417.png

Regards, 
QLB

AilleryO
Super User
Super User

Hi,

 

It could have been easier with real dates, since we could work with time Intelligence, but if you don't have a real date in your data, you can calculate the previous year.

Measure =
Var SelectedYear = MAX ( [Year] )
Var PrevYear = SelectedYear-1
Var PrevPrevYear = PrevYear-1
RETURN
CALCULATE( YourMeasure , Year IN { SelectedYear , PrevYear , PrevPrevYear } )

Shoud do the trick, do not hesitate to tell us if it works..

It could be used as well with a FILTER or CALCULATETABLE...

 

More info on using IN

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!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors