Forum Discussion

Brett007's avatar
Brett007
Icon for Helper III rankHelper III
5 years ago
Solved

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

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 
Need Help
  • selimovd's avatar
    selimovd
    5 years ago

    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
     

     

  • selimovd's avatar
    selimovd
    5 years ago

    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 

4 Replies

  • selimovd's avatar
    selimovd
    Icon for Most Valuable Professional rankMost Valuable Professional
    5 years ago

    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
     

     

    • Brett007's avatar
      Brett007
      Icon for Helper III rankHelper III
      5 years ago

      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's avatar
        selimovd
        Icon for Most Valuable Professional rankMost Valuable Professional
        5 years ago

        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