Forum Discussion

admin11's avatar
admin11
Icon for Memorable Member rankMemorable Member
4 years ago
Solved

How to combine 2 expression into 1 ?

Hi All   I have below 2 seperate expression :-   Show Last 3 Yr = IF ('DATE'[Year] >= Year(TODAY()) - 3, 1, 0) Show Last 4 Yr = IF ('DATE'[Year] >= Year(TODAY()) - 4, 1, 0)   May i know how...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    That's because it needs to be defined as a new calculated table, not a measure or calculated column. See the example I attached previously.

VahidDM's avatar
VahidDM
Icon for Super User rankSuper User
4 years ago

admin11 

My bad.

 

Try this:

Show Last 3 or 4 Yr =
VAR _Y3 =
    YEAR ( TODAY () ) - 3
VAR _Y4 =
    YEAR ( TODAY () ) - 4
RETURN
    IF ( 'DATE'[Year] >= _Y3, 3, IF ( 'DATE'[Year] = _Y4, 4, 0 ) )

 

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

 

 

  • admin11's avatar
    admin11
    Icon for Memorable Member rankMemorable Member
    4 years ago

    VahidDM 

    Great now no more error. 

    Not the situation is when i Tick box = 3 :-

    it display 2019 , 2020 , 2021 , 2022

    If i tick Box = 4 :-

    it display 2018 only

    it is possible to make it display2018 2019 , 2020 , 2021 , 2022

     

    Hope you can share with me

     

    Paul

    it is possible to make it , when i tick box = 4 , 

     

    • AlexisOlson's avatar
      AlexisOlson
      Icon for Super User rankSuper User
      4 years ago

      You aren't going to be able to do that with a calculated column since each year can only belong to at most one of 3 or 4.

       

      You can approach this in a different way though and create a new parameter table to use for the slicer and then define a measure to use as a filter on your date slicer.

       

      For the option to select 1-5 years back, define a new calculated table like this:

      ShowLast = SELECTCOLUMNS ( GENERATESERIES(1, 5), "Year", [Value] )

       

      Then you can define a measure like this which you'll then set as a visual filter for the date table year slicer:

      YearFilter = 
VAR SelectedYear = SELECTEDVALUE ( ShowLast[Year], MAX ('Date'[Year] ) )
VAR CalendarYear = MAX ( 'Date'[Year] )
RETURN
    IF ( YEAR ( TODAY() ) - CalendarYear <= SelectedYear, 1, 0 )

      See the attached file.