LOOKUPVALUE result not stored as DATE when it's a date?

[sublog]

I've been trying to figure out why a measure I created doesn't work when I used a USERELATIONSHIP between two dates. One of the two dates in my measure that uses USERELATIONSHIP  is a calculated column from another unrelated table using LOOKUPVALUE. 

For some reason, the result looks like a date, is formatted like a date, but Power BI sees it as text. I've tried wrapping DATEVALUE around the calculated column and it just doesn't work. I've been searching for over an hour hoping someone else had the same issue. 

Why can't I use LOOKUPVALUE to return a date and it sees it as a date? 

[c_cook28954]

I had an issue similar to this and the comment from @AllisonKennedy  helped me solve it indirectly.

What was happening in my case is that I was referencing two date separate date columns in a new, calculated column. One of those columns contained a date but I didn't think much of it initially as I figured that the relationship would still work if I altered the type to 'Date' from 'Date/Time'.

I ended up figuring out that this was incorrect when I used the DateKey method to extract values from that Calculated column and attempt to format it as a whole number (the process failed due to the inclusion of AM/PM).

Given the above, I went back to the original calculated column and altered part of the expression that was referencing a column with a time in it to include .[Date].

I then retested the issue and found that the problem was solved. 

I'm sure you've resolved your issue, more so noting this down for anyone who encounters it in future.

[V-lianl-msft]

Hi @sublog ,

In my tests, lookupvalue correctly returns a value in date format.

Any chance you can share a PBIX file with dummy data?

Please remove any sensitive data before uploading.

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

[sublog]

Hello, the PBIX file I have is extremely large and would take hours to recreate with sanitized data. I am still stumped why it doesn't work. When I activated that date field between it and the calendar table, it shows the correct results. I have spent so many hours trying to figure this out. 

That table is joined to the calendar table by the lead create date. My second join to the calendar table is the delivery date.  Just trying to show booked revenue on the posting date instead of the create date. I've used this technique before and it works. My only idea why this didn't work was because the date I was joining was a caluclated column lookupvalue date from another table. 

At this point I have given up hope for a solution. I don't have additional time to continue to work on a solution to a single measure. 

[AllisonKennedy]

@sublog  Yeah, possibly not being seen as a date. Please try using the DATEKEY text column method and see if that fixes it?

[AllisonKennedy]

I'm not sure data type is the issue then - you were able to create the relationship between calendar[date] and table2[date] without error? 

I find using date type columns for relationships causes some incorrect results, I think it might be due to time component or some other factor I haven't discovered, but i use DateKey always: https://excelwithallison.blogspot.com/2020/10/datekey-how-to-relate-your-date-data.html

Not sure if that will help you here? 

[sublog]

Yes, there is a relationship between the two tables, although the one I was using was inactive due to another relationship to a different date field in the same table.

The reason I think the datatype is the problem is when the icon next to the lookupvalue field doesn't show as a calendar, and when I try to apply a .date to the end of the column, it doesn't recognize it. That leads me to believe it doesn't see it as a date.

[sublog]

No error. Just the measure doesn't return the correct values. The measure is simple. Just Calculate ( [Measure], USERELATIONSHIP, calendar[date], table2[date] ) The Table2[date] is the lookupvalue. 

[AllisonKennedy]

@sublog  Can you provide more detail on what 'doesn't work' - are you getting an error that says the data type is the problem? 

What else are you trying to do in the measure - can you please share the whole measure here.

When you say the calculated column is formatted as a date, is the data type also date?