Forum Discussion

awitt's avatar
awitt
Icon for Helper III rankHelper III
4 years ago
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...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    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] )
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

awitt , a new column in table1

 

minx(filter(Table2, Table2[semesterDate] >= Table1[OrderDate] && Table2[schoolID] = Table1[SchoolID]), Table2[semesterDate])

  • awitt's avatar
    awitt
    Icon for Helper III rankHelper III
    4 years ago

    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.