Most recent MAX value

[MASF]

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]

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]

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 · Accepted solution]

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]

Great, it works well, thank you very much @FreemanZ  

[MASF]

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]

@MASF  Should the number 115 be back on dates?

[MASF]

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]

@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 ))

[DimaMD]

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)