Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hello all,
I need to create either a calculated column in DAX or a custom column in PowerQuery that compare two date columns in two tables. The output is a column that has the value in the second table that is closest to the first table that is greater than the first table. I am unable to upload the file directly. However, this is the first table:
Here is the second:
And here is the desired output with the comparison being done on the Begin Date in the first table and the Date column in the second
Solved! Go to Solution.
Hi @BryceJones ,
You can achieve your goal by creating a new calculated column in first table with this DAX:
Closest Date =
VAR CurrentBeginDate = 'FirstTable'[Begin Date]
RETURN
MINX(
FILTER(
'SecondTable',
'SecondTable'[Date] > CurrentBeginDate
),
'SecondTable'[Date]
)
Your output will look like this:
Hi @BryceJones ,
You can achieve your goal by creating a new calculated column in first table with this DAX:
Closest Date =
VAR CurrentBeginDate = 'FirstTable'[Begin Date]
RETURN
MINX(
FILTER(
'SecondTable',
'SecondTable'[Date] > CurrentBeginDate
),
'SecondTable'[Date]
)
Your output will look like this:
@BryceJones , A new column in Table 1
Maxx(filter(Table2, Table2[Date] >= Table1[Start Date] && Table2[Date] <= Table1[End Date]) , Table2[Date])
Thank you. To clarify Begin Date and Date are the only dates in my scenario that need this comparison performed on.
User | Count |
---|---|
120 | |
67 | |
66 | |
57 | |
50 |
User | Count |
---|---|
176 | |
83 | |
69 | |
65 | |
54 |