Forum Discussion

Wresen's avatar
Wresen
Icon for Post Patron rankPost Patron
4 years ago
Solved

Find closest date

Hi and thx for reading this.

 

Have search around the forums , have found threads about this but i dont get them to work so that is why i write this

 

I have two tables

and i want to be able to lookup from closest to the date in Rawdate  value from A value in the translate to the Rawdata

 

The result should be like this :

 

I dont  have any relationships beacuse i have no clue how to do this and if its needed (have no restrictions)

 

/Thanks so much for any help

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    It is for creating a new column.

     

     

     

    Value CC =
VAR _datediffmin =
    MINX (
        ADDCOLUMNS (
            FILTER ( Translate, Translate[A ID] = RawData[B ID] ),
            "@datediff", ABS ( DATEDIFF ( Translate[A Date], RawData[B Date], DAY ) )
        ),
        [@datediff]
    )
VAR _value =
    MAXX (
        FILTER (
            ADDCOLUMNS (
                FILTER ( Translate, Translate[A ID] = RawData[B ID] ),
                "@datediff", ABS ( DATEDIFF ( Translate[A Date], RawData[B Date], DAY ) )
            ),
            [@datediff] = _datediffmin
        ),
        Translate[A Value]
    )
RETURN
    _value

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    It is for creating a new column.

     

     

     

    Value CC =
VAR _datediffmin =
    MINX (
        ADDCOLUMNS (
            FILTER ( Translate, Translate[A ID] = RawData[B ID] ),
            "@datediff", ABS ( DATEDIFF ( Translate[A Date], RawData[B Date], DAY ) )
        ),
        [@datediff]
    )
VAR _value =
    MAXX (
        FILTER (
            ADDCOLUMNS (
                FILTER ( Translate, Translate[A ID] = RawData[B ID] ),
                "@datediff", ABS ( DATEDIFF ( Translate[A Date], RawData[B Date], DAY ) )
            ),
            [@datediff] = _datediffmin
        ),
        Translate[A Value]
    )
RETURN
    _value
    • Wresen's avatar
      Wresen
      Icon for Post Patron rankPost Patron
      4 years ago

      Hi Jihwan_Kim 

      Thanks so much , it works perfectly as i wrote but i did not think corretly.

       

      With your formula i was able to figure it out:

      Status =
      CALCULATE(
          MAX('Translate'[A Value]),

          FILTER('Translate','Translate'[A Date] <= 'Rawdata'[B Date] &&
          'Translate'[A ID] = 'Rawdata'[B ID])
      )
       
      Thanks again 🙂