Lookup latest date, but dealing with multiple values

[Creative_tree88]

Hi - I have the attached data.  The data on left represents the source data (shaded yellow), and the data to the right (shaded all blue) represents the desired result using some form of lookup.

I need to basically lookup the corresponding value of the latest date, corresponding to height and weight.  Where there is a blank, I need to use the next available date which has a value and return this value.

I also need to show the measure date associated with each value (height and weight) to determine whether the two dates are close enough to consider accurate enough to use.  

Tricky one...any ideas?  If possible I want to achieve this result using a calculated column using lookup or a derivative of.

Thanks all.  Really appreciate your help as always.

Sample Data 

[vanessafvg · Accepted solution]

Last Valid Weight =
VAR lastdte =
CALCULATE (
MAX ( CustomerWeight[Measure Date] ),
NOT ( ISBLANK ( CustomerWeight[Weight] ) )
)
VAR result =
CALCULATE (
MAX ( CustomerWeight[Weight] ),
CustomerWeight[Measure Date] = lastdte
)
RETURN
IF ( result = 0, "No Height Listed", result )

[v-linhuizh-msft]

Thanks for the replies from vanessafvg.

Hi @Creative_tree88 ,

You can try to create the following four measures:

Latest Height = CALCULATE(SUM('Table'[Height]),FILTER(ALLEXCEPT('Table','Table'[Customer No]),'Table'[Measure Date]=[Measure Height Date]))

Latest Weight = CALCULATE(SUM('Table'[Weight]),FILTER(ALLEXCEPT('Table','Table'[Customer No]),'Table'[Measure Date]=[Measure Weight Date]))

Measure Height Date = CALCULATE(MAX('Table'[Measure Date]),FILTER(ALLEXCEPT('Table', 'Table'[Customer No]),'Table'[Height]>0))

Measure Weight Date = CALCULATE(MAX('Table'[Measure Date]),FILTER(ALLEXCEPT('Table', 'Table'[Customer No]),'Table'[Weight]>0))

Result:

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[vanessafvg]

there might be an easier way to do it i filtered only on these results as the dates gave me some issues on copying and pasting but essentially it gives the correct answer

you need to do a dax calculation 

so get the last valid height

Last Valid Height =
VAR lastdte =
CALCULATE (
MAX ( CustomerWeight[Measure Date] ),
NOT ( ISBLANK ( CustomerWeight[Height] ) )
)
RETURN
CALCULATE (
MAX ( CustomerWeight[Height] ),
CustomerWeight[Measure Date] = lastdte
)

and for the date 

Last Valid Height Date =
CALCULATE (
MAX ( CustomerWeight[Measure Date] ),
NOT ( ISBLANK ( CustomerWeight[Height] ) )
)

see solution attached 

[Creative_tree88]

@vanessafvg That looks good.  I'll do some data quality checks tomorrow and, if it's OK with you, get back to you if there are any further issues I find once I've incorporated this into my report.  Many thanks indeed.

[Creative_tree88]

@vanessafvg Is there a way of introducing text to each measure, to add to the table in the event of there being no data to return.  For example 'No Height Data' where there is nothing.  Many thanks again.

[vanessafvg]

you could but it would not longer be a numeric piece of data, it would need to be come  a text column, it could be solved  by using an if statement.   are you wanting it to be text?

[Creative_tree88]

@vanessafvg It could be text, yes.  Would be interested to see the result.

[Creative_tree88]

Apologies @vanessafvg here's the updated sample.  You are correct, it needs to be based on customer.

Sample Data 

[vanessafvg]

do you want the result at a customer level ie per customerr last height and weight as you didn't include in on your result table