Forum Discussion
Tony2021
Jun 09, 2021Iron Contributor
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 mu...
- Jun 10, 2021
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 () )Please check attached.
HansVogelaar
Jun 09, 2021MVP
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.