cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

New Member

## appear closest date

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

1 ACCEPTED SOLUTION
Super User

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 = MINXALL(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 !

5 REPLIES 5
Super User

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

to display both dates when there is a dead heat.

Super User

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 = MINXALL(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 !

New Member

,

First, thank you

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

Super User

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

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

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.

Community Champion

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))

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors