Max Date per Distinct User which updates with date slider

[brettg]

Hi All,

I haven't come across a post which deals with this issue yet, so I thought I would share. 

I would like to get a Max date for each Distinct User, and this Max date will update based on a date slider I use.

This calculated column for example;

CALCULATE(MAX('Table'[TestDate]),FILTER('Table', 'Table'[User]=EARLIER('Table'[User])))

This will bring back the users latest test in the table as a whole, but if i change the date slider I want this latest test to update within that date range. 

Is this possible?

[Stachu]

calculated column will not change based on slicer, you will need a measure for that
for a measure this simple syntax should work

Measure = LASTDATE('Table'[Test Date])

if you don't won't to see dates in the total row then use this

Measure no totals = 
VAR User = SELECTEDVALUE('Table'[User])
RETURN
CALCULATE(LASTDATE('Table'[Test Date]),'Table'[User]=User

[brettg]

Thanks for your response!

I'm using the second measure you sent across, but it seems to only provide the latest date per row instead of distinct user?

Also, I think I need to use Max('Table' [Test Date]) to capture the latest time of the test as well?

[Stachu]

if you see evaluation by row then I assume you added this syntax as a column rather than measure, and then it won't work properly.

try adding this code as a measure, then you should only be able to see it in visual & field list, not as a column in the underlying table

EDIT - as for the LASTDATE vs MAX, both should do the job, but indeed MAX may be better choice as it doesn't do context transition

[brettg]

I'm hoping this image helps, I have added the syntax as a measure, but I'm trying to show it as a column in a table. 

I would just like this max date measure to update when I change the slider, and to be distinct for each user.

Will this measure not work properly as a column in my table then?

[Stachu]

it's clear now, I didnt expect you wanted to show dates in rows as well

try this syntax

Measure no totals = 
VAR User = SELECTEDVALUE('Table'[User])
RETURN
CALCULATE(MAX('Table'[Test Date]),ALLSELECTED('Table'[Test Date]),'Table'[User]=User)

[brettg]

Thank you for your help with this, it is appreciated!

This measure is only giving a max date per row by the looks of it. Is there anyway to add a 'Group by' to this measure, so I can get one max date per distinct user?

[Stachu · Accepted solution]

it does return the date per user, at least with my data

[brettg]

I see! If you just have the User and the Measure in a table, it does one max date per distinct user.

Can I use this Latest test measure in other visuals?

For example; 

If I want to show the results of the tests in a pie chart, but this pie chart only shows the latest test of each user. How would I include this latest test measure in the filters?

Thanks for your help