Forum Discussion

ngutc's avatar
ngutc
Frequent Visitor
4 years ago
Solved

If date equals specific date than create a constant

Hello, 

 

I'm working with a live database and i would like to create a measure where, if table(date[year]) equals 2005 then make a constant 5 else leave blank. If function does not take the date table and I'm not sure if I'm doing it right. 

 

I want results like this 

20013
2002 
20031
2004 
20055
2006 

 

But create with DAX. 

 

Can someone help? Thank you

constant
dates table
DAX
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi ngutc 
    Please use

    =
SWITCH ( SELECTEDVALUE ( 'Date'[year] ), 2001, 3, 2003, 1, 2005, 5 )

    I noticed maybe you just want to take the right digit of odd years. If this is the case then use 

    =
VAR CurrentYear =
    SELECTEDVALUE ( 'Date'[year] )
RETURN
    IF ( ISODD ( CurrentYear ), VALUE ( RIGHT ( CurrentYear, 1 ) ) )

9 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi ngutc 
    Please use

    =
SWITCH ( SELECTEDVALUE ( 'Date'[year] ), 2001, 3, 2003, 1, 2005, 5 )

    I noticed maybe you just want to take the right digit of odd years. If this is the case then use 

    =
VAR CurrentYear =
    SELECTEDVALUE ( 'Date'[year] )
RETURN
    IF ( ISODD ( CurrentYear ), VALUE ( RIGHT ( CurrentYear, 1 ) ) )
    • ngutc's avatar
      ngutc
      Frequent Visitor
      4 years ago

      Thank you a lot! It worked with the switch function! I actaully wanted the constants for every 5 years, not for the odd ones. 

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        ngutc 

        Then this one

         

         

        =
VAR CurrentYear =
    SELECTEDVALUE ( 'Date'[year] )
RETURN
    IF ( MOD ( CurrentYear, 5 ) = 0, VALUE ( RIGHT ( CurrentYear, 1 ) ) )