Get the max value of a column, limiting the search by a Calendar Table

[Asantos2020]

Hello,

This may sound really basic, but I've run out of neurons by now and I need your help.

I need to get the max value in a column, but the table is filtered by a Calendar Table.

Table1
Date                 Item                Value
01/01/2019      Item1              2

02/01/2019      Item2              3

05/10/2019      Item3              4

I've been using this DAX measure to get the max value:

LowestCost = calculate(min(Table1[Value]);Allexpect(Table1;Table1[Item]))

I now need to get this max value, but considering the date range in the Calendar Table I have filtering Table1.

Thanks in advance!

Antonio Santos

[mwegener · Accepted solution]

Hi @Asantos2020 ,

try this.

HighestCost =
CALCULATE (
    MAX ( Table1[Value] ),
    FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) )
)

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.