Compare value with selected value

[QLB]

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.

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.

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

I have a measure, calculating the sum of sales.

I have a histogram displaying this measure by year.

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

[QLB · Accepted solution]

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. 

Regards, 
QLB

[AilleryO]

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