Problem finding just the latest value in a column

[Clint]

I've got a dataset that looks like the below table and I'm trying to write a measure that will return the last ABD value based. I've tried using this dax:

Remaining Scope =

CALCULATE(

SUMX('Burndowns2',[ABD]),FILTER('Burndowns2',LASTNONBLANK('Burndowns2'[ABD],MAXX('Burndowns2',[Week])

))

)

But this just gives me the sum of all ABD instead of "116" which is the latest value. any insight is appreciated.

[amitchandak · Accepted solution]

@Clint ,

lastnonblankvalue(Table[Date],sum(Table([ABD])))

lastnonblankvalue(Table[Date],Max(Table([ABD])))

refer : https://www.linkedin.com/pulse/five-recent-power-bi-functions-you-should-use-more-often-amit-chandak

[Clint]

Thank you Amit.  The first DAX expression did the trick.  I had tried something like that before but didn't add the "date" part of lastnonblankvalue so thank you.

[Greg_Deckler]

@Clint - Try Lookup Min/Max - https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434

[mahoneypat]

A few ways you can do this.  Here are two.  Both assume you have the ID column in your visual (so additional filtering for that is not needed in these measures).

1. Last Value = LASTNONBLANKVALUE(Table[Date], MIN(Table[ABD]))

2. (Replace Value with ABD and Last with your actual table name)

Last Value =
VAR maxdatewithvalue =
MAXX (
FILTER ( SUMMARIZE ( Last, Last[Date], Last[Value] ), Last[Value] > 0 ),
Last[Date]
)
RETURN
CALCULATE ( MIN ( Last[Value] ), Last[Date] = maxdatewithvalue )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat