Forum Discussion

ramzi_kiswani's avatar
ramzi_kiswani
New Member
4 years ago
Solved

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 appea...
Need Help
  • speedramps's avatar
    speedramps
    4 years ago

    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 ! 

     
speedramps's avatar
speedramps
Super User
4 years ago

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's avatar
    ramzi_kiswani
    New Member
    4 years ago

     ,

    First, thank you 

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

    • speedramps's avatar
      speedramps
      Super User
      4 years ago

      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.