Forum Discussion
Helper VLookup latest date, but dealing with multiple values
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 )
Community Championthere 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_tree88vanessafvg 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?