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-1...
  • 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]
)

     

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

 

 

DimaMD's avatar
DimaMD
Icon for Solution Sage rankSolution Sage
3 years ago

MASF  Should the number 115 be back on dates?

  • 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
      Icon for Solution Sage rankSolution Sage
      3 years ago

      MASF  I'm not sure if this is the correct calculation, but give it a try

      MaxValue = 
VAR maxd = MAX('table'[date])-2
 return
 CALCULATE( sum('table'[val]), FILTER(ALL('table'), [date] = maxd ))