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] )
        )
    )
danextian's avatar
danextian
Super User
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] )
        )
    )
genaussie's avatar
genaussie
Frequent Visitor
2 years ago

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 )

        )