approx match of dates in a calculated column (like in excel vlookup of (true or approx match)

[Anonymous]

i need to calculate Dates id against the dates in a calculated column. there are two sheets ( Activity code 2 & Dates) , both have dates column but dont have exact match except few, so need to calculate approx match of dates ID . 
As in attached sheet Yellow color column i made in excel for dates id by using vlookup of true/approx match function , same result i need in calculated column.
Lookup true.xlsx

[AntrikshSharma]

@Anonymous Try this:

Nearest Date = 
VAR MaxDate = Activity[Max Date]
VAR NearestDate = 
    CALCULATE ( 
        MAX ( Dates[Date] ),
        Dates[Date] <= MaxDate,
        REMOVEFILTERS ( Dates )
    )
VAR DatesID = 
    CALCULATE ( 
        SELECTEDVALUE ( Dates[Date_ID] ),
        Dates[Date] = NearestDate,
        REMOVEFILTERS ( Dates )
    )
RETURN
    DatesID

[Anonymous]

@AntrikshSharma  thank you, it's working , in a calculated column it's showing dates id properly, in a model view when am making relationship it's showing circular dependency as attached, is there any soluation?

[AntrikshSharma · Accepted solution]

@Anonymous Use this:

Date_ID = 
VAR MaxDate = 
    Activity[Max Date]
VAR NearestDate = 
    CALCULATE ( 
        MAX ( Dates[Date] ),
        FILTER ( ALLNOBLANKROW ( Dates[Date] ), Dates[Date] <=  MaxDate ),
        REMOVEFILTERS ( Dates )
    )
VAR DatesID = 
    CALCULATE ( 
        DISTINCT ( Dates[Date_ID] ),
        FILTER ( ALLNOBLANKROW ( Dates[Date] ), Dates[Date] = NearestDate ),
        REMOVEFILTERS ( Dates )
    )
RETURN
    DatesID

[Anonymous]

@AntrikshSharma  Awesome bro, worked properly 

[Anonymous]

@dharmendars007  thank for your response , i have tried this but its not working. can see from the attched 

[dharmendars007]

Hello @Anonymous , 

Use the below DAX formula to get the closest DateID 

ClosestDateID =
VAR CurrentDate = 'Activity Code 2'[DateColumn] -- Replace with your actual date column
RETURN
CALCULATE(MIN(Dates[DateID]), -- Replace with your actual date ID column
FILTER(Dates,Dates[DateColumn] <= CurrentDate -- For closest earlier or equal date))

Ensure that the two tables are related by a common field (like Date) in Power BI's model view. If not, create a relationship based on the appropriate key field, or adjust the DAX formula accordingly to pull the correct data.

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN