Forum Discussion

Tony2021's avatar
Tony2021
Iron Contributor
Jun 09, 2021
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...
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Jun 10, 2021

    Tony2021 

    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.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jun 09, 2021

Tony2021 

If you are not on Mac, creating PivotTable add data to data model

and add DAX measure

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

  • Tony2021's avatar
    Tony2021
    Iron Contributor
    Jun 09, 2021
    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.
    • Tony2021's avatar
      Tony2021
      Iron Contributor
      Jun 10, 2021
      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.
      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jun 10, 2021

        Tony2021 

        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.

Resources