Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Return values for the last date available

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   Stat...
need
Need Help
Need Help DAX
Need Help DAX measure
smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
4 years ago

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's avatar
AlexisOlson
Icon for Super User rankSuper User
4 years ago

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.