Lookup by Datetime

[cawhiteside]

Hi - I have two tables, one that records downtime events at a machine, and another that records when a machine started to produce a certain part number. I'm trying to create a column that finds what part number was associated with the downtime event. Right now I am using this DAX formula:

Material = 
VAR OrderDateTime = [Adjusted_Start]
RETURN CALCULATE(
 FIRSTNONBLANK(ProductionOrderLog[MaterialNo], 0),
 CALCULATETABLE(
 LASTNONBLANK(ProductionOrderLog[StartDateLocal], 0),
 ProductionOrderLog[StartDateLocal] <= OrderDateTime
 )
)

This formula works if a machine started making a part number in the same day, however once a new day is started, it will not pull any part number until a new part is started that day. See below for examples.

Part Number Job Table:

Downtime Event Table:

Can anyone please help me with this? Thanks in advance.

- Connor

[cawhiteside · Accepted solution]

I managed to fix this by deleting the relationship between the date in the table that recorded part number jobs and my custom date table in the report. Thanks anyway for the help!

[Anonymous]

Create column in second table using lookupvalue function.

https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[cawhiteside]

Hi Pravin - I'm afraid that gives me the same problem. If a part started running on a machine at 11pm and the machine goes down the following day at 1am, the part number column will show up blank.

[Anonymous]

Share data in text so that i can work on it.
Also share your expected output.

Thanks
Pravin

[cawhiteside]

Sorry, the LOOKUPVALUE function actually isn't returning anything.  Here's what I tried:

Material = 
LOOKUPVALUE(
 ProductionOrderLog[MaterialNo],
 ProductionOrderLog[StartDateLocal],
 'Production Events'[Adjusted_Start]
)

See below for expected results. The part number column should show 2002155 for all events on entity 9 since the first time it started running (2/27/20 at 7am as shown in second picture).