cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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 Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors