Best Practice for Max Calculation with a Condition

[genaussie]

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 is a cleaner way that is considered best (or better) practice:

Formula =

    LOOKUPVALUE('List'[Office],

        'List'[Project], 'List'[Project],

        'List'[Period], CALCULATE(MAX('List'[Period]), ALLEXCEPT('List', 'List'[Project])))

Thanks in advance for any insight.

[danextian · Accepted solution]

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] )
        )
    )

[genaussie]

Hi @danextian confirming this works. It is useful to have alternative solutions 🙂

Another option is to utilise 2x VAR:

VAR MaxPeriodByProject = CALCULATE ( MAX ( 'List'[Period] ), ALLEXCEPT ( 'List', 'List'[Project] ) )

VAR CurrentProject = 'List'[Project]

RETURN

    CALCULATE (

        MAX ( 'List'[Office] ),

        FILTER (

            ALL ( 'List' ),

            'List'[Period] = MaxPeriodByProject

                && 'List'[Project] = CurrentProject )

        )

[elitesmitpatel]

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]

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."