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 t...
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 ) ) )
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
Icon for Community Champion rankCommunity Champion
4 years ago

ngutc 

Then this one

 

 

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

 

 
  • ngutc's avatar
    ngutc
    Frequent Visitor
    4 years ago

    what i get is this 

     
     
     
     
     
    20000
    2001 
    2002 
    2003 
    2004 
    20055
    2006 
    2007 
    2008 
    2009

     

    2010

    0

     

    I want to be able to declare for each five years a different constant. 

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Hi ngutc 
      Would yu be more specific? Do you have a list?

      • ngutc's avatar
        ngutc
        Frequent Visitor
        4 years ago

        It's like target numbers for each five years. For example i want for year target 5 and in year 2010 target 11. Tha values do not have any pattern and it's really random. But I think the switch function does it's purpose! Thank you!