Forum Discussion

genaussie's avatar
genaussie
Frequent Visitor
2 years ago
Solved

Best Practice for Max Calculation with a Condition

Hi All,   The current formula is relatively simple and works to return the latest Office for the Project based on the Period, but it involves using both LOOKUPVALUE and MAX. I'm wondering if there ...
Need Help
  • danextian's avatar
    danextian
    2 years ago

    Hi genaussie 

    Try this:

    CalcColumn =
VAR MaxPeriodByProject =
    CALCULATE ( MAX ( 'List'[Period] ), ALLEXCEPT ( 'List', 'List'[Project] ) )
RETURN
    CALCULATE (
        MAX ( 'List'[Office] ),
        FILTER (
            ALL ( 'List' ),
            'List'[Period] = MaxPeriodByProject
                && 'List'[Project] = EARLIER ( 'List'[Project] )
        )
    )
elitesmitpatel's avatar
elitesmitpatel
Icon for Solution Supplier rankSolution Supplier
2 years ago

Try this

LatestOfficeByPeriod =
VAR CurrentProject = SELECTEDVALUE('List'[Project]) -- Get the currently selected project
RETURN
LOOKUPVALUE(
'List'[Office],
'List'[Project], CurrentProject,
'List'[Period], EARLIER('List'[Period])
)

  • genaussie's avatar
    genaussie
    Frequent Visitor
    2 years ago

    Hi, I don't think this works as I get the error message, "EARLER/EARLIEST refers to an earlier row context which doesn't exist."