How to find maximum value just for past 1 year?

[dragonus]

So I have a table with data spanning 10 years. However, I would like to find the highest value just for the past 1 year. Tried using MAX() but understand that it returns values from the entire 10 year span which is not what I am looking for.

[amitchandak]

@dragonus , do you have date ?

With date

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
or

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH))

With only year

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year]))) // Date can be year table only

[dragonus]

@amitchandak 

I tried your last formula 

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year]))) // Date can be year table only

[amitchandak]

@dragonus ,Can you share sample data and sample output in table format? 

[dragonus]

I currently have this:

Measure = calculate(sum(table[value]),filter('table','table'[value] = max ('table'[value])))

Which gives me the highest value for the entire table

However, I would just like highest value for the past year.

I have also tried 

Measure = calculate(sum(table[value]),filter('table','table'[value] = max ('table'[value])),filter('table','table'[date].[year] = max('table'[date].[year])))

Which gave me values that needs to fulfill the requirements of being both the largest & existing in the year. Thus, it returns blanks if the largest values do not exist in the past year.

However, I need values in the past year that is the largest.

[Fowmy · Accepted solution]

@dragonus 

Try this measure, it takes one year from today's date and return the highest values.

Max Value = 
VAR PAST1YR = EDATE(TODAY(),-12)

RETURN
MAXX(
    FILTER(
        DATA,
        DATA[Date] >= PAST1YR && DATA[Date] <= TODAY()
    ),
    DATA[Value]
)
  

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[dragonus]

Sure

Date	Category	Value
01/02/2019	A	2039
02/02/2019	A	2948
01/02/2020	A	1233
01/02/2020	A	1211

Sample Output

Category	Max Value for past year
A	1233