Forum Discussion
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 mu...
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.
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.
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.
- nice. Works perfectly. I now see how I can add another DAX measure.
I wanted to keep this in the pivot, which is what your solution does i/o a Index and match, which is quite crafty as well.