cancel
Showing results for
Did you mean: Frequent Visitor

## Latest date in a matrix

Hello,
I have test data like this: 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!

1 ACCEPTED SOLUTION  Super User

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
)
)
)

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.

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.

Best regards, JiHwan Kim

4 REPLIES 4  Super User

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
)
)
)

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.

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.

Best regards, JiHwan Kim Frequent Visitor

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!  Super User

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.

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.

Best regards, JiHwan Kim  Super User

@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 )  