Forum Discussion

vehau1's avatar
vehau1
Helper I
7 years ago
Solved

Get value from row in the same table based on last date

This should be trivial, but I'm dragging the hairs off my head! I can't get to display the most recent value!!!!!! why do I get every other value when I try to filter on last nonblank date?   I hav...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi vehau1

    Create measures

    column B = SUM(Sheet2[columnb])

last non-blank date = CALCULATE(MAX(Sheet2[date]),FILTER(ALLSELECTED(Sheet2),[column B]<>BLANK()))

FLAG = IF([last non-blank date]=MAX(Sheet2[date]),1,0)

last non-blank value = CALCULATE(MAX(Sheet2[columnb]),FILTER(ALL(Sheet2),[FLAG]=1))

     

    Best Regards

    Maggie

v-juanli-msft's avatar
v-juanli-msft
Community Support
7 years ago

Hi vehau1

Create measures

column B = SUM(Sheet2[columnb])

last non-blank date = CALCULATE(MAX(Sheet2[date]),FILTER(ALLSELECTED(Sheet2),[column B]<>BLANK()))

FLAG = IF([last non-blank date]=MAX(Sheet2[date]),1,0)

last non-blank value = CALCULATE(MAX(Sheet2[columnb]),FILTER(ALL(Sheet2),[FLAG]=1))

 

Best Regards

Maggie

  • vehau1's avatar
    vehau1
    Helper I
    7 years ago

    Great, thanks! Wasn't as straight forward as I thought, but I will study the codes.