cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Measure to return a value with the most recent date

I want to write a measure to return the most current value for an asset based on the last date available:

 Table1 Asset ID Value Period A 1500 1-Dec-20 A 4500 1-Mar-21 A 2000 1-Jun-21 B 500 1-Dec-20 B 150 1-Mar-21 B 100 1-Jun-21 C 600 1-Dec-20 C 1000 1-Mar-21 C 900 1-Jun-21 Result Asset Id Current Value Period A 2000 1-Jun-21 B 100 1-Jun-21 C 900 1-Jun-21

1 ACCEPTED SOLUTION
Super User

@shelleye This is Lookup Min/Max, like this:

``````Measure Lookup Max Simple =
VAR __Table = 'Table1'
VAR __Max = MAXX(__Table,[Period])
RETURN
MAXX(FILTER(__Table,[Period] = __Max),[Value])``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
3 REPLIES 3
Frequent Visitor

@Greg_Deckler thank you!!!

Super User

@shelleye No problem, I actually grabbed that from my test PBIX for my external tool, Microsoft Hates Greg's Quick Measures. I have 4 different lookup min/max formulas in there that make these kinds of calculations a breeze.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Super User

@shelleye This is Lookup Min/Max, like this:

``````Measure Lookup Max Simple =
VAR __Table = 'Table1'
VAR __Max = MAXX(__Table,[Period])
RETURN
MAXX(FILTER(__Table,[Period] = __Max),[Value])``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...