 SOLVED

# Show MAX per month but only the MAX value

Hello,

How can I show only the MAX values in the attached?  The pivot is also showing the other dates that are not the MAX.  Kindly see attached.  This is  simplified example and my dataset is much larger.

thank you.

6 Replies

# Re: Show MAX per month but only the MAX value

You cannot do that with a pivot table.

I have attached a version with array formulas that work in all versions of Excel.

If you have Microsoft 365, simpler formulas are possible, and Power Query would be another option.

# Re: Show MAX per month but only the MAX value

If you are not on Mac, creating PivotTable add data to data model ``````Latest Amt=
VAR maxAmt=MAX(Table1[Amt])
VAR maxAmtCo=CALCULATE(MAX(Table1[Amt]), ALL(Table1[Date]))
RETURN
IF(maxAmt=maxAmtCo, maxAmt, BLANK() )``````

as and use it in values # Re: Show MAX per month but only the MAX value

wow both are quite ingenious.
Sergei: if I already have a DAX measure added, it doesnt look as though I can add another one more without affecting the current DAX? I tested and it overwrites the other DAX I already had.

Hans: that works nicely. thanks.

# Re: Show MAX per month but only the MAX value

Sergei: also if I have 2 MAX VALUES that are duplicate then it lists both of them however I would only want to list the initial one. FYYI: Hans solution accounts for duplicates and uses the first case.
best response confirmed by Tony2021 (Super Contributor)
Solution

# Re: Show MAX per month but only the MAX value

Not sure I understood what exactly do you mean in first question, how do you create measures? In general it could be as many measures as you wish To check earliest date with max amount for the company we may use something like

``````Max Amt:=
VAR biggestAmount =
MAX ( Table1[Amt] )
VAR earliestDate =
MIN ( Table1[Date] )
VAR maxAmt =
CALCULATE (
MAX ( Table1[Amt] ),
ALL ( Table1[Date] )
)
VAR minDate =
CALCULATE (
MIN ( Table1[Date] ),
ALL ( Table1[Date] ),
Table1[Amt] = maxAmt
)
RETURN
IF (
biggestAmount = maxAmt
&& minDate = earliestDate,
biggestAmount,
BLANK ()
)``````