Skip to main content
cancel
Showing results for 
Search instead 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

Reply
shelleye
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 IDValuePeriod
A15001-Dec-20
A45001-Mar-21
A20001-Jun-21
B5001-Dec-20
B1501-Mar-21
B1001-Jun-21
C6001-Dec-20
C10001-Mar-21
C9001-Jun-21
   
Result  
   
Asset IdCurrent ValuePeriod
A20001-Jun-21
B1001-Jun-21
C9001-Jun-21

 

 

 

 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
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])

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
shelleye
Frequent Visitor

@Greg_Deckler thank you!!!

@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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
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])

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.