How to use selectedvalue and column values.

[EricHulshof]

Hello everyone. I have a dataset looking like this:

Date ordered:

Date:

1-1-2012

5-6-2013

6-8-2014

This tabel has lots of dates. I use the YEAR value of this table to select a year. 

Next i have a table with:

Number	Name	Active from	Active till
111	Name1	1-1-2012	31-12-2100
222	Name2	1-1-2012	31-12-2012
333	Name3	1-1-2012	31-12-2013
4556	Name4	1-1-2014	31-12-2100
5775	Name5	1-1-2014	31-12-2100
6574	Name6	1-1-2014	31-12-2100
87544	name7	1-1-2014	31-12-2100

So now i want to create a field, showing wich number was active at the selected date. 

Example i select 2013. this should show up.

Number	Name	Active from	Active till	WasActive
111	Name1	1-1-2012	31-12-2100	1
222	Name2	1-1-2012	31-12-2012	0
333	Name3	1-1-2012	31-12-2013	1
4556	Name4	1-1-2014	31-12-2100	1
5775	Name5	1-1-2014	31-12-2100	1
6574	Name6	1-1-2014	31-12-2100	1
87544	name7	1-1-2014	31-12-2100	1

I tried doing this with a measure, but i cannot use columns in a measure. The i tried it using a calculated column, but i can not use selectedvalue in a calculated column. 

The calculated column looks like this:

WasActive = IF(AND([Selectedvalue] >= Table2[Active from].[Year];[Selectedvalue] <= table2[Active till].[Year]);1;0) 

Where i now have the simple measure:

Selectedvalue = 2013 

I used to have the selected value take the value from the slicer, but that ofcourse does not comply with the calculated column.

 

Anyone knows how i can fix this?

 

Thankyou.

[v-eachen-msft · Accepted solution]

Hi @EricHulshof ,

You need to create a measure if you want dynamic values.

Measure =
VAR a =
    YEAR ( SELECTEDVALUE ( 'Table'[Active from] ) )
VAR b =
    YEAR ( SELECTEDVALUE ( 'Table'[Active till] ) )
RETURN
    IF (
        SELECTEDVALUE ( 'Table (2)'[Year] ) >= a
            && SELECTEDVALUE ( 'Table (2)'[Year] ) <= b,
        1,
        0
    )

Here is the result.

Here is my test file for your reference.