Forum Discussion

awitt's avatar
awitt
Helper 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] )
Jihwan_Kim's avatar
Jihwan_Kim
Super User
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] )