topic Re: How to find closest date after certain date in DAX? (Example shown) in DAX Commands and Tips

How to find closest date after certain date in DAX? (Example shown)

whatisdata96 — Thu, 13 Oct 2022 20:09:24 GMT

Hi all -

I'm looking to create a conditional column in Power Query which would analyze 3 columns to output a 'looked up value' (date). This was probably not explained very well. But this is what I want:

I'm looking to return the NEXT nearest date for all rows with column 'type' = I. If it says, I, that means we want the next nearest date for where 'type' is O (matching with 'number'. For all rows with 'O' we can leave it as blank.

Is there a way to do this via condtional column? Or would I have to do a custom column in Power Query or DAX? Thank you in advance.

Re: How to find closest date after certain date in DAX? (Example shown)

Anonymous — Thu, 13 Oct 2022 21:22:18 GMT

try this measure :

var daterech=selectedvalue(Table[Date])

return

calculate(Min(Table[Date]), filter(all(Table), Table[Date]>daterech))

Re: How to find closest date after certain date in DAX? (Example shown)

v-yueyunzh-msft — Fri, 14 Oct 2022 02:05:50 GMT

Hi , whatisdata96

Here are the steps you can refer to :

(1)My test data is the same as yours.

(2)We can create a calculated column in your table:

Column = var _cuurent_date = 'Table'[Date] var _tO =SELECTCOLUMNS( FILTER( 'Table' , 'Table'[Type]= "O") , "Date" , [Date]) var _compare =ADDCOLUMNS( CROSSJOIN( {_cuurent_date} , _tO) , "days" ,ABS( DATEDIFF([Date] ,[Value] ,DAY))) var _min_days = MINX(_compare, [days]) var _mindate_table = DISTINCT( FILTER( _compare , [days] = _min_days)) return IF([Type]="I", CONCATENATEX(_mindate_table,[Date]), BLANK())

(3)Then we can meet your need , the result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly