LOOKUPVALUE returns blank only for specific columns

[Anonymous]

Hi All, 

I've got a problem with a LOOKUPVALUE function, wondering if anyone can help me understand it better? Because of some specific reporting requirements I need a variable in a Measure to calculate what the Relative Period No from my Calendar table is for yesterday. For this, I wrote the following formula: 

LOOKUPVALUE('Calendar by Calendar Date'[Financial Relative Period],'Calendar by Calendar Date'[Calendar Date],TODAY()-1)

The values I would expect this to return are a 0 or a -1, but today it should be a 0. 

Instead, the formula returns blank, despite the fact that it is correctly identifying what TODAY()-1 is, the Calendar table is also populated with values for the Relative Period field, and also if I replace this field with another, the formula returns the correct value.

As an example if I swap it for the actual Period No: LOOKUPVALUE('Calendar by Calendar Date'[Financial Period No],'Calendar by Calendar Date'[Calendar Date],TODAY()-1) --> this then returns a value of 2, which is correct. 

Both the Relative Period and the Period No fields will repeat values for each Calendar Date of the Period, which doesn't seem to cause an issue when looking for the specific Period No and not the Relative Period No. 

Filtering on the 12/03/2023 these are the values in my table for both fields. Both formatted as Whole numbers... Literally no difference between them from a data perspective. 

Any ideas why one returns blank but the other doesn't? 

[Anonymous · Accepted solution]

It looks as though this was a fluke with PBI, I re-uploaded the source dataset (this was a report using a shared dataset) and refreshed the report and the formula is now correctly finding the right value. 

[amitchandak]

@Anonymous , I thin case it will return

'Calendar by Calendar Date'[Financial Relative Period],

You can create a new column like

New columm=

convert( Countx(filter('Calendar by Calendar Date','Calendar by Calendar Date'[Calendar Date]=TODAY()-1), 'Calendar by Calendar Date'[Financial Relative Period]) , boolean)

[Anonymous]

Hi Amit, 

Apologies not sure I understand your suggestion. I don't want an additional field, I've got all the columns I need in my data, I just want to understand why I'm able to look up one field but not another when technically there is no difference between them. 

Thank you