Forum Discussion
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 date but I need to figure what the closest semester start date is.
So in the example below, the yellow column would be calculated in the Orders Table. The first order has an order date of 1/1/2022 and the closest start date is 1/6/2022 so there you go. I would also be ok if this result wasn't the date, bit the SemesterID. PBIX file here.
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] )
3 Replies
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.
