Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

LookupValue not returning all values

I am at a loss and have dug through Google looking for something similiar.

 

I used this formula to create a column

 

FY22%Comp = MROUND( IF(Preconstruction[TaskStartDate]>DATE(2022,06,30),0,if(DATE(2022,06,30)>Preconstruction[TaskFinishDate],1,DATEDIFF(Preconstruction[TaskStartDate],DATE(2022,06,30),MONTH)/Preconstruction[Precon Duration])),.10)
 
This gives me a % complete for a give projects and rounds it to the nearest 10% 
 
From there I created a custom table that has a column for % complete so that I can run a LOOKUPVALUE formula between the two tables.
 
It works perfect except for 30% 60% and 70%
 
There is no direct relationship between these 2 tables.
 
Here is my lookup value formula
 
FY23%SpendPC = LOOKUPVALUE(Curves[Back Loaded Current],Curves[Percent Complete],Preconstruction[FY23%Comp])
 
It works for every single % except for the 3 mentioned above. I have pulled the values out of both tables and dropped them in Excel to verify they indeed match and they do.
 

 

 

Has anyone ever had this happen, or is something off with my formula?

1 Reply

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    3 years ago

    I'd guess there isn't a single unique match (i.e. zero or multiple matches) or possibly a floating point error issue.

     

    Without actually looking at the tables and preferably the pbix, it's difficult to say for sure. Can you share a dummy file that reproduces this issue?