from measure to colum

[Anonymous]

hi i have a calculated measure but want a column instead because of a filter that i need

see below my calculated column.

First Project =
VAR mydate =
CALCULATE (
MIN ( Projecten[start_date]);
FILTER (
CALCULATETABLE ( VALUES ( Projecten) );
Projecten[start_date] >= Projecten[Datum in dienst]
)
)RETURN
CALCULATE (
MIN ( Projecten[end_customer]);
FILTER (
CALCULATETABLE ( VALUES ( Projecten ));
Projecten[start_date]= mydate
)
)

the story behind the calculated column

Table 1
Name	Date in Service	First Project	Second Project
Alex K	1-1-2018	I want here the first project  of Table 2 of Alex K based on project start date vs Date In service	Here  the second
Jay kay	1-2-2018
Bernd leo	1-3-2018
Table 2
Name	name Project	Project Start Date	Project End Date
Alex K	AA technology	2-1-2018	14-2-2018
Alex K	AB technology	15-2-2018	11-3-2018
Alex K	AC technology	12-3-2018	14-2-2018
Jay kay	AA technology	15-2-2018	19-2-2018
Jay kay	AB technology	20-2-2018	11-3-2018
Jay kay	AC technology	12-3-2018	14-4-2018
Jay kay	AD technology	15-4-2018	1-3-2018
Bernd leo	AA technology	2-3-2018	11-3-2018
Bernd leo	AB technology	12-3-2018	14-3-2018
Bernd leo	AC technology	15-3-2018

i have 2 tables and in table one i want to  have the first and scond project of table 2 based on the nearest Project start date in comparison with the date in service

example: Alex K first project is AA technology because 2-1-2018 is the first nearest date tot the date in service 1-1-2018

[v-juanli-msft · Accepted solution]

Hi @Anonymous

Based on my test, this formula can be applied in a calculated column and can get the expected result as you want, you can also modify a little to use  it in a measure. So, I don't understand what are you seeking for?

Measure = 
VAR mydate =
CALCULATE (
MIN ( Sheet1[Project Start Date]),
FILTER (
CALCULATETABLE ( VALUES ( Sheet1) ),
Sheet1[Project Start Date]>= RELATED(Sheet2[Date in Service])
)
)RETURN
CALCULATE (
MIN ( Sheet1[name Project]),
FILTER (
CALCULATETABLE ( VALUES ( Sheet1 )),
Sheet1[Project Start Date]= mydate
)
)

Best Regards

Maggie