cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper III

How to find maximum value just for past 1 year?

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.

1 ACCEPTED SOLUTION
Super User

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

Did I answer your question? Mark my post as a solution! and hit thumbs up
6 REPLIES 6
Super User

@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

Helper III

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

However, it returned me the latest value for each row instead of the largest value for the past 1 year.

Super User

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

Helper III

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.

Super User

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

Did I answer your question? Mark my post as a solution! and hit thumbs up
Helper III

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

Announcements

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors