Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Calculate max date over multiple dates in a row

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

Need Help
Show and Tell

8 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

    Hi Anonymous

     

    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] )
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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