Lookup returning wrong Date value

[AvPowerBI]

Hi,

I'm not sure what my Lookup is doing but I want it to show blank if there is no match. 

So if ActualStartDateKey is showing as Minus 1 (-1) then I want the Calculated DAX Column Start Time to be BLANK

This is my DAX Calculated Column

Start Time = LOOKUPVALUE('Calendar'[Date Key], 'Calendar'[ISOdateKey], 'Table1'[ActualStartDateKey]) + LOOKUPVALUE('Time'[FullTimeString24], 'Time'[TimeKey], 'Table1'[ActualStartDateTimeKey])

and this is what the current results look like, for some reason it is showing a date of 1899-12-30 but there is no value of -1 on the Calendar Table

SalesOrderNo	Customer No	ActualStartDateKey	ActualStartDateTimeKey	Start Time
ABC123	224	-1	0	1899-12-30 00:00:00
ABC124	1003	20201124	80833	24/11/2020 08:08:33

[PhilipTreacy]

Hi @AvPowerBI 

Your lookups are resulting in 0.  When you convert 0 to a DateTime you get 30 Dec 1899 00:00:00.

You're doing 2 LOOKUPVALUE though?  If the first one doesn't find a match it'll return Blank but what is the 2nd LOOKUPVALUE returning?  Presumably the 0 value you see in Start Time?

Please share some data/PBIX file.

regards

Phil

[AvPowerBI]

the second lookup, the underlining table I believe has a default value of a 0 if there is no time so will never bet showing as -1. So it's basically the first lookup, if that has -1 then I want the concatenated end result to be blank

[PhilipTreacy]

Hi @AvPowerBI 

OK so you just need to test the value returned by your 1st LOOKUPVALUE, then proceed accordingly

Start Time = 

VAR _DateKey = LOOKUPVALUE('Calendar'[Date Key], 'Calendar'[ISOdateKey], 'Table1'[ActualStartDateKey])

RETURN

   IF ( _DateKey = -1, BLANK(),  _DateKey + LOOKUPVALUE('Time'[FullTimeString24], 'Time'[TimeKey], 'Table1'[ActualStartDateTimeKey]) )

Regards

Phil

[AvPowerBI]

Thanks for your help so far, it is still returning the Date of 30/12/1899 00:00:00 and as you said before it must be due to the second lookup which gets the time, so how do I amend what you have provided to say if  _DateKey = -1 and Time Lookup = 0 THEN BLANK Else bring back the DateTime

[PhilipTreacy]

Hi @AvPowerBI 

Use this

Start Time = 

VAR _DateKey = LOOKUPVALUE('Calendar'[Date Key], 'Calendar'[ISOdateKey], 'Table1'[ActualStartDateKey])

VAR _TimeKey = LOOKUPVALUE('Time'[FullTimeString24], 'Time'[TimeKey], 'Table1'[ActualStartDateTimeKey])

RETURN

   IF ( AND( _DateKey = -1 , _TimeKey = 0 ), BLANK(),  _DateKey + _TimeKey )

Regards

Phil

[AvPowerBI]

Hi @PhilipTreacy 

I get the below error message:

DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.

[PhilipTreacy · Accepted solution]

Hi @AvPowerBI 

Ah, the _TimeKey var is a string.  Try this

Start Time = 

VAR _DateKey = LOOKUPVALUE('Calendar'[Date Key], 'Calendar'[ISOdateKey], 'Table1'[ActualStartDateKey])

VAR _TimeKey = VALUE( LOOKUPVALUE('Time'[FullTimeString24], 'Time'[TimeKey], 'Table1'[ActualStartDateTimeKey]))

RETURN

   IF ( AND( _DateKey = -1 , _TimeKey = 0 ), BLANK(),  _DateKey + _TimeKey )

Regards

Phil

[AvPowerBI]

There are two lookups, one is looking up a date value and the other looking up the time value and then concatenating the two values together

[PhilipTreacy]

Hi @AvPowerBI 

OK, have you checked to see what each of those lookups is returning?

If the first one returns blank and the 2nd one returns 0 then blank + 0 = 0 which is what you are seeing.

0 as a DateTime is 30 dec 1899 00:00:00

Regards

Phil