Calculate The Last Or Max Value Within A Table Of Data Using DAX In Power BI

In this tutorial, we review how you can work out the last value from a particular dimension in a table in Power BI.

This is quite a unique insight but a relevant one from many pieces of analysis that you might require in Power BI.

We work through examples of the MAXX and FILTER functions. These are two important functions in the DAX language that can be used in many situations to extract very interesting insights from your data.

Good luck.

Sam

***** Video Details *****
2:27 - using calculated columns
6:13 - using a measure

***** Learning Power BI? *****

***** Related Links *****

***** Related Course Modules *****

***** Related Support Forum Posts *****

[EnterpriseDNA]

Learning Power BI?

Related Links

Related Course Modules

Related Support Forum Posts

[vaibhavtandon]

Great video Sam! Would love to see more short videos like this explaining and resolving the real world bau issues.

[rdg]

Thanks. Can you take this a concept a step further and demonstrate how to pull back a separate attribute from the most recent survey?

[LucianoVazBH]

Excellent video, I've been looking for a content like this for a long time. how would you count the rows after this filter? I need to count how many rows that meet these criteria. need help.

And count accumulating to max calendar date.

[fernandofuzinattoportes]

You save thks!! This is fantastic resolution!! I needed to capture the last tag from the last session!

[kanwalsingh]

Hi Sam, awesome teaching. Would you be able to help me if I do not want max value in all row when you are creating calculated column. Just want to show the result on the row which is last / Max.

or, measure to count the last / Max value please.

Thanks

[safifaruqi]

Thank you so much this helped me big time! if i wanted to add multiple filters to this current measure how would you go about approaching it.

[justair]

Awesome! Exactly what I was looking for. I actually needed to do this based on two columns. So I added another VAR for the other column and threw it in the filter. Works great!!

[MrDavisv]

I really enjoy the videos! Can you do an example on how to reference the previous row in DAX (e.g., what is the difference in days between current row’s sales dates and the previous sales date?)

[Praaxx]

Hello Sam,


Thank you for all the videos, they are very informative. A couple of question on this one.

I noticed when you add additional lines to your DAX formula window it plays the chime. If you use SHIFT+ENTER instead of ALT+ENTER it won't play the chime, at least it doesn't for me and I was happy to find that trick because the chime can be annoying.

On the measures you use to calc the MAX date, wouldn't it be better (smaller memory usage) to use just the two needed columns in the filter statement rather than filtering the entire table?

Max Date Measure =
VAR CurrentUser = SELECTEDVALUE ( Table1[Name] )
RETURN
MAXX (
FILTER ( ALL ( Table1[Name], Table1[Date Taken] ), Table1[Name] = CurrentUser ),
Table1[Date Taken]
)

[wiegunadjatmika]

Hi Sam,
Could you share the trick how to get last value (text) on every single date or others dimension?

[vishakhakumar]

Really glad to find this after a lot of scouring the internet.
Thank you!

[sonamverma]

Hi Sam, I have been watching your power bi videos for a while. They are a great help for my learning.
If you could please let me know how would we get the last entry for each Name for each month, that would be a great help.

[branzo]

This is great. Now I need to find the previous from the max in the same month.

[abdulazad]

It is of very great help, calculated column calculation is working as expected, whereas the same DAX not working for measure, where DAX measure is giving wrong output. I am using switch statement to hide and show measures in matrix. Can you please help me in resolving the DAX measure calculation to get the expected output.

[eknol]

This is what i was looking for. Thanks.

[nikakalichava]

this one actually hooked me up. tried to do similar with summarize, addcollums but did not work, then I came up with ieasier solution:
CALCULATE(
MAX('Table'[Date] ),
ALLEXCEPT( 'Table', 'Table'[Emp] )
)

[jarrahalrowayyeh]

Thanks for the very informative video. i just have one Q, what if then i want the sum of the latest value ? could you please answer this question

[sorinbuzuriu]

Thanks, Sam!
I've used this logic to create a similar measure for the latest date (with data available) for every month.
Cheers!

[jontybuk]

Could this be used to select the role/post of an employee from a historical table of employee roles which has a start date and end date for each role the employee has been in? Any links to other videos would be greatly appreciated