Calculation using time references

[sfink22]

I'm trying to write a calculation that compares date/times between two tables, and transfers over a value from another column. The first table contains info for a product used in a production system, with sample readings taken multiple times throughout the day. The second table contains information on the raw material used by vendor and time of sample taken.  

Table 1 (production samples) looks as follows:

Date/Time	Var 1	Var 2	Var 3

And Table 2 (Raw material samples) looks as follows:

Date/Time	Supplier
3/24/20 3:14 PM	A
3/24/20 3:45 PM	B
3/24/20 5:00 PM	B
3/24/20 5:30 PM	A

The sample times are not correlated - i.e. the raw material samples are taken at a different frequency than the production samples. I need to know what supplier was in use when the production sample was taken. I want a calculation that will look at the time that the production sample was taken, find the next closest time from Table 2 that it does not exceed, and return that supplier. For example, if I had a production sample taken at 5:15 PM, it would run through until it found the first raw material sample after 5:15, and return the supplier name from the previous line. In this case, it would return supplier B, since the 5:30 PM sample exceeds 5:15.

[v-frfei-msft · Accepted solution]

Hi @sfink22 ,

Please create a calculated column as below.

Column = 
VAR currentrowtime = [date/time]
VAR maxdate =
    CALCULATE (
        MAX ( 'Raw material samples'[Date/Time] ),
        FILTER (
            'Raw material samples',
            'Raw material samples'[Date/Time] < currentrowtime
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Raw material samples'[Supplier] ),
        FILTER (
            'Raw material samples',
            'Raw material samples'[Date/Time] <= currentrowtime
                && 'Raw material samples'[Date/Time] >= maxdate
        )
    )

Please check the pbix as attached.

[sfink22]

Thank you for your help @v-frfei-msft ! I tried your solution, and changed the table names to match my own, but the column is returning blanks. Not sure if it has to do with the fact that I merged separate date and time columns into one? I'm not sure how to attach the workbook so you can see. At this point I have a broken link to the original material, so I'm not able to do anything in query editor.

[v-frfei-msft]

Hi @sfink22 ,

Please create calculated column in data view as the picture below. Btw, you can share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive for Business and share the link here.