Find closest date to a certain date

[Anonymous]

Hello,

As a new Power BI user I am really enjoying the ability to create variables within a measure.  Currently I have 3 dates that I need to determine if they come after the end date.  If one or all of these dates come after the end date I need to find the date that is closest to the end date.  So far I'm able to find the minimum of 2 dates, but I'm not sure how to capture all 3 dates within a measure to see which one is the closest to the end date.   Here is the code I have below so far: 

Final Date = 

VAR Release = IF(MAX(Dates[released_date]) > MAX(Dates[End_date]), MAX(Dates[released_date]))
VAR Assigned = IF(MAX(Dates[Assigned_date]) > MAX(Dates[End_date]), MAX(Dates[Assigned_date]))
VAR Sheet = IF(MAX(Dates[sheet_date]) > MAX(Dates[End_date]), MAX(Dates[sheet_date]))

Return
    MIN(Release, Assigned)

[sroy_16]

Try using this measure.

Final Date =
VAR Release = IF(MAX(Dates[released_date]) > MAX(Dates[End_date]), MAX(Dates[released_date]), BLANK())
VAR Assigned = IF(MAX(Dates[Assigned_date]) > MAX(Dates[End_date]), MAX(Dates[Assigned_date]), BLANK())
VAR Sheet = IF(MAX(Dates[sheet_date]) > MAX(Dates[End_date]), MAX(Dates[sheet_date]), BLANK())

VAR DateTable =
FILTER(
{
Release,
Assigned,
Sheet
},
NOT(ISBLANK([Value]))
)

RETURN
MINX(DateTable, [Value])

Let me know if the solution works.

[rajendraongole1 · Accepted solution]

Hi @Anonymous - Can you please try the below measure to get the all the 3 date to get the close to the end date.

Final Date =
VAR EndDate = MAX(Dates[End_date])
VAR Release = IF(MAX(Dates[released_date]) > EndDate, MAX(Dates[released_date]), BLANK())
VAR Assigned = IF(MAX(Dates[Assigned_date]) > EndDate, MAX(Dates[Assigned_date]), BLANK())
VAR Sheet = IF(MAX(Dates[sheet_date]) > EndDate, MAX(Dates[sheet_date]), BLANK())

VAR DatesAfterEndDate =
FILTER(
{
Release,
Assigned,
Sheet
},
NOT(ISBLANK([Value]))
)

RETURN
MINX(DatesAfterEndDate, [Value])

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!