Forum Discussion
LOOKUPVALUE returns blank only for specific columns
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.
Any ideas why one returns blank but the other doesn't?
3 Replies
- amitchandak
Super User
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)
