find nearest date
Results 1 to 5 of 5

Thread: find nearest date

  1. #1
    Board Regular
    Join Date
    May 2018
    Posts
    63
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default find nearest date

    Hello everyone,

    I am looking for a Lookup-function in PowerPivot/ Dax to get the nearest date for a specific date.

    I have the following two tables:

    TblVal
    Date Value
    01.01.2019 100
    28.01.2019 101
    27.02.2019 102
    30.03.2019 103


    TblCAl
    Date Month Value
    01.01.2019 Jan
    02.01.2019 Jan
    03.01.2019 Jan
    04.01.2019 Jan
    01.02.2019 Feb
    01.03.2019 Mar
    01.04.2019 Mai


    The objective/ challange is to get for each date (tblCal) the respective value from the tblVal.
    So the 01.01.2019 should get the value 100.

    So every date between 01.01.2019 and 28.01.2019 should get the value 100.

    Does anyone know how to manage this?



    Best Regards
    Joshua

  2. #2
    Board Regular
    Join Date
    Jul 2014
    Location
    The Netherlands
    Posts
    1,248
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: find nearest date

    Hi,

    try a calculated column on Tblcal with this:
    =
    VAR mydate =
    CALCULATE (MIN ( Tblval[Date] ); FILTER ( CALCULATETABLE ( VALUES ( Tblval ) ); Tblval[Date] >= Tblcal[Date]))
    RETURN
    CALCULATE (MIN ( Tblval[Value] ); FILTER ( CALCULATETABLE ( VALUES ( Tblval ) ); Tblval[Date]= mydate ))
    --------------------------------------------------------------------------------
    If you found my answer helpful, please hit the Like or Thank button.

    Please follow the forum Rules and Guidelines and use Code tags around your VBA code.

    Remember: test VBA code always on a copy of your file because usually you can't use <undo>

  3. #3
    Board Regular
    Join Date
    May 2018
    Posts
    63
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: find nearest date

    Hey jorismoerings!
    Thanks for your approach!

    Is there maybe a sintax issue with RETURN? I put the function into the column in powerpivot. Is that right?

  4. #4
    Board Regular
    Join Date
    Jul 2014
    Location
    The Netherlands
    Posts
    1,248
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: find nearest date

    Hi @joslaz

    No, there's no issue but perhaps you've broken the formula into 2 calculated fields.
    If so, that's not the case, you need to copy the entire string as 1 formula into the calculated field and not in 2 separate fields
    --------------------------------------------------------------------------------
    If you found my answer helpful, please hit the Like or Thank button.

    Please follow the forum Rules and Guidelines and use Code tags around your VBA code.

    Remember: test VBA code always on a copy of your file because usually you can't use <undo>

  5. #5
    Board Regular
    Join Date
    May 2018
    Posts
    63
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: find nearest date

    Perfect! It works!
    Thanks

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •