Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
awitt
Helper III
Helper III

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

awitt_0-1648009734276.png

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new column.

 

Picture1.png

 

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] )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

View solution in original post

3 REPLIES 3
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new column.

 

Picture1.png

 

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] )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

amitchandak
Super User
Super User

@awitt , a new column in table1

 

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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. 

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.