appear closest date

[ramzi_kiswani]

hello community 

I want to show the closest date to the current (9/4/2022) date from the below column

12/24/2022

9/20/2022

5/12/2022

12/5/2022

appear to a visual card or make a measure to appear every day on the closest day, ex: it will appear on the closest day 20/9/2022

any idea ?!

[speedramps]

@PaulDBrown just privare messaged me with a suggetion to use CONCATENATEX

to display both dates when there is a dead heat.

Click here to download the example 

[speedramps · Accepted solution]

Click here to download the solution 

How it works ...

This will get you the difference in days between 2 days

Difference =DATEDIFF(SELECTEDVALUE(Dates[Date]), [Today], DAY)

However it could return a minus or a plus, so use the ABS command to remove the +/-

ABS Difference = ABS(DATEDIFF(SELECTEDVALUE(Dates[Date]),[Today], DAY))

We then need to find the small difference, using the MINX and ALL commands

Min ABS Difference = MINX( ALL(Dates),  [ABS Difference])

Then set a flag = 1 if the date has the closest value 

IsClosest =

VAR mydifference = [ABS Difference]

RETURN

IF(mydifference= [Min ABS Difference],1)

We can then filter just the dates that have IsClosest = 1

Sometimes there might be dead heat for example 19/08/2002 and 20/09/2022 both have 16 days difference from 04/09/2022. So you need to decide which date you choose if there is a dead heat 

This will get the first

Min Closest date =

VAR winningdates = FILTER(Dates,[IsClosest] = 1)

RETURN

CALCULATE(  MIN(Dates[Date]),  winningdates)

Thanks for reaching out for help.

I put in a lot of effort to help you, now please quickly help me by giving kudos.

Remeber we are unpaid volunteers and here to coach you with Power BI and DAX skills and techniques, not do the users job for them. So please click the thumbs up and accept as solution button. 

If you give someone a fish then you only give them one meal, but if you teach them how to fish then they can feed themselves and teach others for a lifetime.  I prefer to teach members on this forum techniques rather give full solutions and do their job. You can then adapt the technique for your solution, learn some DAX skills for next time and soon become a Power BI Super User like me. 

One question per ticket please. If you need to extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

[ramzi_kiswani]

 ,

First, thank you 

in the " Difference = ABS(DATEDIFF(SELECTEDVALUE " return with no data 

[speedramps]

You have added the DAX column rather than a DAX measure.

Please see and use the example I provided ....

Click here to see and use an example 

Thanks @ramzi_kiswani for the thumbs up.

Please can click Accept as Solution to close the ticket.

I see another super user has also provided a smilare answer.
So you can decide which is best for your purpose. 
Ideally we try keep to just one super user per case but sometimes the response times overlap.

[PaulDBrown]

Try:

closest =

VAR _REF = TODAY()

VAR _DIFF = DATEDIFF(MAXTable[Date]), _REF, DAY)

VAR _NUM = IF(_DIF > 0, _DIF * -1, _DIF)

_MX = MAXX(ALL(Table[Date]), _NUM)

RETURN 

CALCULATE (MAX(Table[Date]), FILTER(Table, _NUM = _MX))