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 )

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.