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
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.
Solved! Go to Solution.
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] )
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.
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] )
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.
@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.
User | Count |
---|---|
115 | |
95 | |
87 | |
76 | |
65 |
User | Count |
---|---|
138 | |
113 | |
110 | |
98 | |
93 |