cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

DAX Show Value based on latest date with multiple filters

I'm fairly novice in DAX and need help with the following forumla:

What i need is a measure that will display the latest (most recent date) hours for a specific unit and type:

If i wanted Unit 1 Type A, i would want it to display 1200 (this is the hours from the most recent entry)

MAX function will not work as the hours can be higher on ealier dates so i need the latest hours from the date/time

 

Date/TimeUnitTypeHours
01/01/2020 1:00Unit 1A1000
01/02/2020 8:00Unit 2B500
01/10/2020 10:00Unit 1A1100
01/11/2020 8:00Unit 2A600
01/12/2020 8:00Unit 1A1200
2 REPLIES 2
v-shex-msft
Community Support
Community Support

HI @Anonymous,

You can use the following measure formula to get lastest hours based on the current unit and type group:

 

Lastest Hour =
VAR filtered =
    CALCULATETABLE (
        t3,
        ALLSELECTED ( t3 ),
        VALUES ( T3[Unit] ),
        VALUES ( T3[Type] )
    )
VAR _max =
    MAXX ( filtered, [Date/Time] )
RETURN
    MAXX ( FILTER ( filtered, [Date/Time] = _max ), [Hours] )

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
amitchandak
Super User IV
Super User IV

@Anonymous 

Try

 

VAR __id = MAX ( 'Table'[id] )
VAR __Type = MAX ( 'Table'[Type] )

VAR __date = CALCULATE ( MAX( 'Table'[date] ), ALLSELECTED ( 'Table' ),  'Table'[id] = __id,'Table'[Type] = __Type  ) 
RETURN CALCULATE ( MAX ( 'Table'[value] ), VALUES ( 'Table'[id] ), 'Table'[id] = __id,'Table'[Type] = __Type , 'Table'[date] = __date )

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.