Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

First value of another table base on date

Hi, I have 2 tables. Table 1 contains IDBoard and the date/time for each: Table 2 contains production run with a start date and end date:   In table 2 I want to add a new column with ...
Need Help
  • v-eachen-msft's avatar
    v-eachen-msft
    7 years ago

    Hi Anonymous ,

     

    You use this measure:

    First IDBoard =
VAR start1 =
    SELECTEDVALUE ( table2[start] )
VAR end1 =
    SELECTEDVALUE ( table2[end] )
RETURN
    CALCULATE (
        MIN ( table1[idBoard] ),
        table1[timeStamp] >= start1
            && table1[timeStamp] <= end1
    )

    Best Regards,

    Eads

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-eachen-msft's avatar
v-eachen-msft
Icon for Community Support rankCommunity Support
7 years ago

Hi Anonymous ,

 

You can create a measure in your second table.

First IDBoard =
VAR start1 =
    SELECTEDVALUE ( table2[start] )
VAR end1 =
    SELECTEDVALUE ( table2[end] )
RETURN
    CALCULATE (
        MIN ( table1[idBoard] ),
        DATESBETWEEN ( table1[timeStamp], start1, end1 )
    )

Then you can get result you want in a visual.

 

Best Regards,

Eads

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi v-eachen-msft ,

    This measure give me an error: duplicate dates dont't work with DATESBETWEEN function. 

    Any ideas?
    Thanks

    • v-eachen-msft's avatar
      v-eachen-msft
      Icon for Community Support rankCommunity Support
      7 years ago

      Hi Anonymous ,

       

      You use this measure:

      First IDBoard =
VAR start1 =
    SELECTEDVALUE ( table2[start] )
VAR end1 =
    SELECTEDVALUE ( table2[end] )
RETURN
    CALCULATE (
        MIN ( table1[idBoard] ),
        table1[timeStamp] >= start1
            && table1[timeStamp] <= end1
    )

      Best Regards,

      Eads

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.