Reply
Regular Visitor
Posts: 23
Registered: ‎08-10-2018
Accepted Solution

Max Date per Distinct User which updates with date slider

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?

 

Capture1.JPG


Accepted Solutions
Super User
Posts: 866
Registered: ‎06-23-2016

Re: Max Date per Distinct User which updates with date slider

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

View solution in original post


All Replies
Super User
Posts: 866
Registered: ‎06-23-2016

Re: Max Date per Distinct User which updates with date slider

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
Regular Visitor
Posts: 23
Registered: ‎08-10-2018

Re: Max Date per Distinct User which updates with date slider

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?

 

 

Highlighted
Super User
Posts: 866
Registered: ‎06-23-2016

Re: Max Date per Distinct User which updates with date slider

[ Edited ]

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

Regular Visitor
Posts: 23
Registered: ‎08-10-2018

Re: Max Date per Distinct User which updates with date slider

Capture111.PNG

 

 

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?

 

Super User
Posts: 866
Registered: ‎06-23-2016

Re: Max Date per Distinct User which updates with date slider

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)
Regular Visitor
Posts: 23
Registered: ‎08-10-2018

Re: Max Date per Distinct User which updates with date slider

 

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?

 

 

Super User
Posts: 866
Registered: ‎06-23-2016

Re: Max Date per Distinct User which updates with date slider

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

Regular Visitor
Posts: 23
Registered: ‎08-10-2018

Re: Max Date per Distinct User which updates with date slider

[ Edited ]

 

 

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

 

 

Capture23322.PNG