How to use selectedvalue and column values.

[Anonymous]

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.

[Anonymous · Accepted solution]

Hi @EricHulshof,

If I understand you correctly, SELECTVALUE() function might be helpful for you.

Please check following measure and see if the result achieve your expectation:

Measure =

var sv = RIGHT(SELECTEDVALUE('Date'[Date]),4)

return

IF(RIGHT(MAX('Table'[Active from]),4)<=sv&&RIGHT(MAX('Table'[Active till]),4)>=sv,1,0)

The Date column in my sample data is Text type, you can use FORMAT() function to change type if it is Date type in your data.

Result would be shown as below:

BTW, Pbix as attached, hopefully works for you.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Allright!

i knew about the selectedvalue function, i just couldnt figure out how to properly use the column values. Max and Right where the things i was looking for!

Thanks alot!