Return values for the last date available

[Anonymous]

Hi everyone,

I am making a report with the following tables:

ProjectList

Project ID	Project name	Project Owner
1	Project X	John Doe
2	Project Y	Jane Doe

And

StatusUpdates

Project ID	Date	Update
1	20/11/2021	Fixed issue 1
2	22/11/2021	Fixed issue 2
1	21/11/2021	Fixed issue 3
2	24/11/2021	Fixed issue 4

"ProjectList" filters "StatusUpdates" based on the column "ProjectID". I essentially imported the first table as a table in my report, and I would like to achieve that by clicking on one of the values in that table, it returns the string value in the column "Update" of the table "StatusUpdates" of the latest date. So essentially, when you would click on project 1 in the table, it would return the value "Fixed issue 3" on a card, which is the third record in the second table.

I already tried with SELECTEDVALUE() but I'm not able to fix the issue. Can someone help me?

[smpa01]

@Anonymous  returned and attached

Measure =
VAR _0 =
    MAX ( 'Project Status'[Created] )
RETURN
    CALCULATE (
        MAX ( 'Project Status'[Status Budget] ),
        FILTER (
            VALUES ( 'Project Status' ),
            'Project Status'[Created]
                = CALCULATE ( _0, ALLEXCEPT ( 'Project Status', 'Project Status'[ProjectId] ) )
        )
    )

@AlexisOlson  thanks !!!

[AlexisOlson]

Not quite what I meant. Once a variable is defined, adjusting its filter context with CALCULATE doesn't do anything, so your DAX is equivalent to 

Measure =
VAR _0 = MAX ( 'Project Status'[Created] )
RETURN
    CALCULATE (
        MAX ( 'Project Status'[Status Budget] ),
        FILTER ( VALUES ( 'Project Status' ), 'Project Status'[Created] = _0 )
    )

I was suggesting something like this:

Measure =
VAR ProjectLastCreated =
    CALCULATE (
        MAX ( 'Project Status'[Created] ),
        ALLEXCEPT ( 'Project Status', 'Project Status'[ProjectId] )
    )
RETURN
    CALCULATE (
        MAX ( 'Project Status'[Status Budget] ),
        'Project Status'[Created] = ProjectLastCreated
    )

[smpa01]

@Anonymous  you can use a measure like this

Measure =
CALCULATE (
    MAX ( StatusUpdates[Update] ),
    FILTER (
        VALUES ( StatusUpdates ),
        StatusUpdates[Date]
            = CALCULATE (
                MAX ( StatusUpdates[Date] ),
                ALLEXCEPT ( ProjectList, ProjectList[Project ID] )
            )
    )
)

[AlexisOlson]

This appears to work but I'd recommend defining a variable for MaxDate for several reasons:

1. Performance. You don't have to recalculate for each row of the table you're filtering.
2. Robustness. Avoid potential trouble arising from a context transition inside FILTER.
3. Readability. Broken up a bit, the code is often easier for humans to understand.

[Anonymous]

Hi, unfortunately it does not work for meand it even seems to return the first registered values, not the last ones...

[smpa01]

@Anonymous pbix is attached. Please post your pbix if it does not help. I will take a look.

[Anonymous]

Hi, yours works indeed. I took out the sensitive data out of mine and replaced with dummy data. I tried to keep it as original as possible so some of the column names are different but its the same principle. I am putting in a wetransfer link as I cannot upload files in these posts yet : https://we.tl/t-FevGPdVk3Y

[VahidDM · Accepted solution]

HI @Anonymous 

Download this file: https://gofile.io/d/A1SLih

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/