First value of another table base on date

[FRG]

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 the first IDBoard stamp after Start date of table 2. The expecting result should be:

Note: Table 1 contains around 30 millions rows!

How can I do that?
Thanks

[v-eachen-msft]

Hi @FRG ,

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.

[FRG]

Hi @v-eachen-msft ,

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

Any ideas?
Thanks

[v-eachen-msft · Accepted solution]

Hi @FRG ,

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.

[vanessafvg]

@FRG  can you provide the data in  text format?  (ie no pics)