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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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

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

Bilalmakki_0-1728116940444.png

 

1 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

View solution in original post

6 REPLIES 6
AntrikshSharma
Super User
Super User

@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

AntrikshSharma_0-1728127226866.png

 

Anonymous
Not applicable

@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?

Bilalmakki_0-1728130093632.png

 

@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
Not applicable

@AntrikshSharma  Awesome bro, worked properly 

Anonymous
Not applicable

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

Bilalmakki_0-1728119002796.png

 

dharmendars007
Super User
Super User

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 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.