Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
UmairKamal
Frequent Visitor

Lookupvalue bringing invalid dates

Dear Users,

 

I have a table of payments and a table of cases. although there exists a one to many relationship, RELATED does not seem to be working.

 

There are multiple payments for the same case with different payment dates.

 

what I am trying to achieve is to to extract the first payment date for any case. for this I used the following formula:

 

Payment Date = LOOKUPVALUE(Paid[Payment Date],Paid[Case-WE Month],Case[Case-WE Month], min(Paid[Payment Date]))

 

I am receiving the following results. as you can see that it is giving me dates which precede the date of the data itself i.e. the dataset starts from October 2022, whilst I am getting the invalid date of 2 October 2020. as a result, I am getting negative payment days as well. 

 

what can I do to fix the issue? 

 

UmairKamal_0-1679573862466.png

 

5 REPLIES 5
HotChilli
Super User
Super User

This is a calculated column? or a measure?

Please provide sample data and show the desired result 

I have cracked the code, thank you for your interest and responses. 

This is a caluclated column and not a measure, please see the example with dummy data below, the desired result is in RED.

 

UmairKamal_0-1679578568312.png

 

HotChilli
Super User
Super User

This is a calculated column?

LOOKUPVALUE needs an odd number of parameters (there are 4 in the provided code).

It should be 

(return value, search column1, search value1, search column2, search value2........ 

the last expression in the code is for alternate result, that is why it is even number of parameters. considering the issue explained, you could advise me a better solution if the one I used is not.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors