If date equals specific date than create a constant

[ngutc]

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 

2001	3
2002
2003	1
2004
2005	5
2006

But create with DAX. 

Can someone help? Thank you

[tamerj1 · Accepted solution]

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]

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]

@ngutc 

Then this one

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

[ngutc]

what i get is this 

2000	0
2001
2002
2003
2004
2005	5
2006
2007
2008
2009
2010	0

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

[tamerj1]

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

[ngutc]

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! 

[tamerj1]

I Understand but can you provide me this list where you have the year and the target value. I mean it is easier to import this table locally and create a relationship with your table from the live database. Otherwise this list would be helpful even to create an independent measure.

[ngutc]

The list with values are 

2000	0
2001
2002
2003
2004
2005	6
2006
2007
2008
2009
2010	10
2011
2012
2013
2014
2015	15
2016
2017
2018
2019
2020	21
2021
2022

There is literally no pattern how the values go and it's really random (only that it's for every five years), so with the switch measure I think it could be done. If in the future there will be more values, I would consider creating a table and their relationship in the live database. 

If there would be a measure that could be more indepedent, I'm not sure how. 

If you have any ideas, that would be great, but you helped me a lot. 

Thank you!

[tamerj1]

Hi @ngutc 
easiest is to have this list as a table in your model connected with the main table or with the date table. If you can provide more context about your model and what exactly is the ultimate required result, then I can advise how exactly to plug in this table into your model and use use it to generate the required output. Then if this table is updated in anytime the results will be updated automatically