How to lookup unique keys which has multiple values

[Praj]

Hello Everyone,

I am trying to lookup for keys which has multiple values in the table. 

For example, 

Below is my source table, where contract start is the date when the contract started for the user and the review date is the date when the user reviewed his/her service.

I am trying to findout the review date for each user based on when it occured. For example, User 1 had two reviews, one within 90 days of contract date and other was during 90 and 180 days of contract. Below is my output table I created manually. 

I am trying to find out if there is any DAX formulas to calculate these. Any help is appreciated. 

TIA

Cheers,

Praj

[TomasAndersson · Accepted solution]

Hi!

You can add some filter conditions with DATEDIFF() too only look at a certain period, for example:

ReviewDateBetween90-180Days = 
CALCULATE(
    Max('Table'[Review Date]),
    ALLEXCEPT('Table','Table'[User]), //Look at all entries for the specific user
    DATEDIFF('Table'[Contract Start],'Table'[Review Date],DAY) >= 90 &&
    DATEDIFF('Table'[Contract Start],'Table'[Review Date],DAY) < 180
)

Note that this finds the latest (MAX()) review date within each period. If there can be several reviews for the same user within the same period, you might have to modify something.

Hope this helps!