Forum Discussion
Solved
Nearest Date from Other Table
I have two tables, one is an orders table with an order date and the other is a semester table with a semester start date. I am wanting to evaluate how close the order date is to the semester start d...
Hi,
Please check the below picture and the attached pbix file.
It is for creating a new column.
Desired result CC = VAR currentschoolid = OrderTable[SchoolID] VAR currentorderdate = OrderTable[OrderDate] VAR semesterdatedifftable = ADDCOLUMNS ( FILTER ( SemesterIDTable, SemesterIDTable[SchoolID] = currentschoolid ), "@orderdate", currentorderdate ) VAR datediffcolumn = ADDCOLUMNS ( semesterdatedifftable, "@diff", ABS ( DATEDIFF ( [@orderdate], SemesterIDTable[SemesterStartDate], DAY ) ) ) VAR datediffmin = MINX ( datediffcolumn, [@diff] ) VAR filtermin = FILTER ( datediffcolumn, [@diff] = datediffmin ) RETURN MAXX ( filtermin, SemesterIDTable[SemesterStartDate] )Desired result semesterID CC = VAR currentschoolid = OrderTable[SchoolID] VAR currentorderdate = OrderTable[OrderDate] VAR semesterdatedifftable = ADDCOLUMNS ( FILTER ( SemesterIDTable, SemesterIDTable[SchoolID] = currentschoolid ), "@orderdate", currentorderdate ) VAR datediffcolumn = ADDCOLUMNS ( semesterdatedifftable, "@diff", ABS ( DATEDIFF ( [@orderdate], SemesterIDTable[SemesterStartDate], DAY ) ) ) VAR datediffmin = MINX ( datediffcolumn, [@diff] ) VAR filtermin = FILTER ( datediffcolumn, [@diff] = datediffmin ) RETURN MAXX ( filtermin, SemesterIDTable[SemesterID] )
awitt , a new column in table1
minx(filter(Table2, Table2[semesterDate] >= Table1[OrderDate] && Table2[schoolID] = Table1[SchoolID]), Table2[semesterDate])
So this worked when the orderdate was before the start date. But in a case where something was ordered the day after start, this column is picking up not that start day the day before but rather the next start date which might be months away.