Forum Discussion

Creative_tree88's avatar
Creative_tree88
Icon for Helper V rankHelper V
1 year ago
Solved

Lookup latest date, but dealing with multiple values

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 Help
  • vanessafvg's avatar
    vanessafvg
    1 year ago

     

    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 )

     

vanessafvg's avatar
vanessafvg
Icon for Community Champion rankCommunity Champion
1 year ago

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