Filtering a Calculated column to only show the Latest Date result.

[Brett007]

I am using this column in a matrix that is built off data from an associated table (connected via Program Number) 

The problem is my filter does not work.  It shows all dates rather than just the latest Approved date for the review.  I would like it to be a Blank cell if it is not the latest Approved.  I do not want the Matrix to only show the values that exsist.  I want to see the blank rows as well.  That is why I want to use a column. 

Latest Approved Review =

--If the Date Approved is the Latest date for that Program Number then  Return the Date Approved and a similar calculated column for the associated Review in a matrix.

CALCULATE(

LASTDATE(Reviews[Date Approved]),

FILTER(All(Reviews[Program Number]),Reviews[Program Number] = Earlier(Reviews[Program Number])))

Sample dataset:
Troubleshooting PBI 

[selimovd · Accepted solution]

Hey @Brett007 ,

check the following calculated column. With that you will get the latest [Date Approved] by [Program Number]:

Latest Approved Review NEW =
VAR vProgramNumber = Reviews[Program Number]
RETURN
    CALCULATE(
        MAX( Reviews[Date Approved] ),
        Reviews[Program Number] = vProgramNumber,
        ALLEXCEPT(
            Reviews,
            Reviews[Program Number]
        )
    )

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[selimovd · Accepted solution]

Hey @Brett007 ,

check the following calculated column. With that you will get the latest [Date Approved] by [Program Number]:

Latest Approved Review NEW =
VAR vProgramNumber = Reviews[Program Number]
RETURN
    CALCULATE(
        MAX( Reviews[Date Approved] ),
        Reviews[Program Number] = vProgramNumber,
        ALLEXCEPT(
            Reviews,
            Reviews[Program Number]
        )
    )

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Brett007]

If I wanted to use a similar formula to pull in the 'Review' that corrisponds to the date, how would I do that.  I tried to manipulate the current formula, but I think the Max() is what I'm getting stuck on.  Thanks.

[selimovd · Accepted solution]

@Brett007 

Same approach, you save the date of the current row in a variable (like the program number) and then get the MAX from the table that is filtered to the desired date 

[Brett007]

Thank you.