Calculate max date over multiple dates in a row

[Erwin]

Hi all,

Please review below table, called "Requests".

I need to calculate, for each individual row in the table, the largest value / newest date of the columns [Creation date], [Start date], [Delivery date] and [Verification date].

Normally, in PBI, I would create a calculated column "MaxDate" with the following formula:

MaxDate = 
VAR temp={[Creation date],[Start date],[Delivery date],[Verification date]}

RETURN
   MAXX(temp,[Value])

This formula, when used in PBI, would give me the following outcome:

However, because of my clients tooling constraints, I have to work with Power Pivot in Excel 2013. The above formula will not work in this tool.

I've searched for and tried different options, but can't seem to get it working.

Can you help me?

Many thanks in advance!

Erwin

[Zubair_Muhammad]

Hi @Erwin

You might be able to use the ROW and UNION function in Excel 2013 to achieve the same result

=
VAR temp =
    UNION (
        ROW ( "Dates", [Creation date] ),
        ROW ( "Dates", [Start date] ),
        ROW ( "Dates", [Delivery date] ),
        ROW ( "Dates", [Verification date] )
    )
RETURN
    MAXX ( temp, [Dates] )

[Erwin]

Hi Zubair,

Unfortunately the UNION function is also not accepted by Excel 2013.

This is the type of solution I'm thinking about:

MAXX(
        <SomeTable, dynamically created to contain four dates per row>,
        [SomeTable]
)

Do you have any other ideas?

Erwin

[Zubair_Muhammad]

@Erwin

Can you use GENERATESERIES in Excel 2013?

[Zubair_Muhammad]

@Erwin

The old fashioned way should work

=
MAX (
    MAX ( MAX ( [Creation date], [Start date] ), [Delivery date] ),
    [Verification date]
)

[Zubair_Muhammad]

@Erwin

Max takes only 2 arguments. So we have to nest them

[Erwin]

Hi Zubair,

Still doesn't work. In your example you are still putting four dates in one MAX function, which is not allowed.

I'm trying to calculate over a dynamic table using MAXX and ROW, but I can't seem to work what the expression should be (argument 2 of MAXX).

=MAXX( 
            ROW(
                      "Create",MAXX(VALUES(Requests[Request]),Requests[Creation date]),
                      "Start",MAXX(VALUES(Requests[Request]),Requests[Start date]),
                      "Deliver",MAXX(VALUES(Requests[Request]),Requests[Delivery date]),
                      "Verify",MAXX(VALUES(Requests[Request]),Requests[Verification date])
            ),
            <What goes here???>
)

[Zubair_Muhammad]

Hi

I am not putting 4 values in one function
Please take a look carefully

I am taking max of max of max with 2 columns in each

[Erwin]

Nope, just GENERATE and GENERATEALL