Forum Discussion

MASF's avatar
MASF
Frequent Visitor
3 years ago
Solved

Most recent MAX value

Hello

 

Anybody knows how to create a DAX measure to get the LATEST (most recent) MAX value?

For example, in the set of dates bellow, the most recent max value is 115 and it happened on the 2022-10-09. 

 

Many thanks,

Miguel

 

  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi MASF 

     

    Now i see, try to write a measure like this:

    LastestPeak = 
VAR _table =
FILTER(
    TableName,
    VAR _val = [val]
    VAR _date = [date]
    VAR _valpre =
        MINX(
            FILTER(
                TableName, 
                _date -1 = [date]
            ),
            [val]
        )
    RETURN
    IF(_val>_valpre, TRUE, FALSE)
)
VAR _datemax =
MAXX(_table, TableName[date])
RETURN
MAXX( 
    FILTER(
        _table, 
        TableName[date]=_datemax 
    ), 
    TableName[val]
)

     

9 Replies

  • MASF's avatar
    MASF
    Frequent Visitor
    3 years ago

    Hi Dima

     

    Thanks for your reply. 

    I am looking to know, in the entire dataset, what is the latest max value. Not the max value in the dateset. Without date filters.

    In the data I provided:

    1. the latest date is the 2022-10-11, and it's value is 113.

    2. The previous day 2022-10-10 the value is the same.

    3. On the previous 2022-10-09 the value is higher 115 

    4. On the previous day, 2022-10-08 the value decreases, it's 110, thus I don't want to look any furthe back, I want the value of 115.

     

    Hope it clarifies 🙂

    Miguel

     

     

      • MASF's avatar
        MASF
        Frequent Visitor
        3 years ago

        Yes. Looking at the most recent date, and then going backwords, return the first max  (or, as soon as the value decreases (for the previous day), stop looking back and return the previous value). 

         

  • DimaMD's avatar
    DimaMD
    Solution Sage
    3 years ago

    Hi MASF If I understood your task correctly, then try such an event. 
    If you are using a date table then you will need to replace the Min and Max measures from the date table

    latest = 
 VAR _1 = CALCULATE( SUM('table'[val]), 'table'[date] = MIN('table'[date]))
 VAR _2 = CALCULATE( SUM('table'[val]), 'table'[date]  = MAX('table'[date]))
 Return
 IF( _1 >= _2, _1)

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    hi MASF 

     

    are you expecting two measures like this:

    MaxValue = CALCULATE(MAX(TableName[val]), ALL(TableName))

MaxValueDate =
VAR _MaxValue = [MaxValue]
RETURN
MAXX(
    FILTER(
    TableName, 
    TableName[val] = MaxValue
    ),
    TableName[date]
)
    • MASF's avatar
      MASF
      Frequent Visitor
      3 years ago

      Hi FreemanZ 

       

      Thank you for your time.

      That would give the max value in all dataset, it's not what I need. 

      As mentioned, I am looking for the latest max value, before the value drops again. 

      Considering the example I gave, the value I am looking is 115 - your measure gives the max in the dataset, 120. 115 is the latest max value in the date range give, since on the previous day (2022-10-08) the value drops to 110.

      Thanks, Miguel

       

       

      • FreemanZ's avatar
        FreemanZ
        Super User
        3 years ago

        hi MASF 

         

        Now i see, try to write a measure like this:

        LastestPeak = 
VAR _table =
FILTER(
    TableName,
    VAR _val = [val]
    VAR _date = [date]
    VAR _valpre =
        MINX(
            FILTER(
                TableName, 
                _date -1 = [date]
            ),
            [val]
        )
    RETURN
    IF(_val>_valpre, TRUE, FALSE)
)
VAR _datemax =
MAXX(_table, TableName[date])
RETURN
MAXX( 
    FILTER(
        _table, 
        TableName[date]=_datemax 
    ), 
    TableName[val]
)