Latest date in a matrix

[nenadpekec]

Hello,
I have test data like this:

NOTE: You can download it at https://gofile.io/d/xPHIGM

The report should be simple, one slicer (Date) and a Matrix visual.
The problem that I'm unable to solve is to display the latest date in a matrix (column), based on user’s selection in the slicer.

In the examples below, I need to display only dates underlined with green line (the latest date per user, based on selection in the slicer).

Example 1:

Example 2:

I have tried with RANKX function in a measure but none of my attempts worked.

Any suggestion is appreciated!

[Jihwan_Kim · Accepted solution]

Hi, @nenadpekec 

Please check the below picture and the sample pbix file's link down below.

Value Total =
VAR currentuser =
MAX ( Users[User] )
VAR currentparameter =
MAX ( Parameters[Parameters] )
VAR latestdate =
CALCULATE (
MAX ( 'Values'[Date] ),
FILTER (
ALLSELECTED ( 'Values' ),
'Values'[User] = currentuser
&& 'Values'[Parameters] = currentparameter
)
)
RETURN
CALCULATE (
SUM ( 'Values'[Value] ),
KEEPFILTERS (
FILTER (
ALL ( 'Values' ),
'Values'[User] = currentuser
&& 'Values'[Parameters] = currentparameter
&& 'Values'[Date] = latestdate
)
)
)

https://www.dropbox.com/s/ufn9epk67zsk9qe/nenadpekec.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[RudraKumar]

Thank you for the solution..

In my case your solution is working but it is taking more time for the visual to load.Shall we do the same thing in a form of calculated column instead of a measure.

[nenadpekec]

Thank you very much, Kim.

This worked as a charm!

Would you mind explaining what was the reason for splitting data into multiple tables?
P.S. I have updated the measure and tried using columns from the same table, and it worked!

[Jihwan_Kim]

Hi, @nenadpekec 

Thank you for your feedback.

It is just a practicing purpose for me. 🙂

I try to create a star schema data model, if possible, even it is a small sample dataset.

In order to get the result in a small sample dataset, it is not quite relevant ( actually, not efficient) to do it in my way. However, I always try and practice creating dimension tables - fact table with proper relationships. 

Thanks. 

[amitchandak]

@nenadpekec , assume you have a measure value, Try a new measure or include value calculation in last calculate

Measure =
VAR __id = MAX ('Table'[User] )
VAR __date = CALCULATE ( MAX('Table'[NUM] ), ALLSELECTED ('Table' ), 'Table'[User] = __id )
CALCULATE ( [value], VALUES ('Table'[User] ),'Table'[User] = __id,'Table'[NUM] = __date )