Forum Discussion

QLB's avatar
QLB
Frequent Visitor
3 years ago
Solved

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.

 

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's avatar
    QLB
    3 years ago

    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

2 Replies

  • AilleryO's avatar
    AilleryO
    Memorable Member
    3 years ago

    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

  • QLB's avatar
    QLB
    Frequent Visitor
    3 years ago

    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