Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
dragonus
Helper III
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

@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

 

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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

6 REPLIES 6
amitchandak
Super User
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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

@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

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

 

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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.

@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

 

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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Sure

 

DateCategoryValue
01/02/2019A2039
02/02/2019A2948
01/02/2020A1233
01/02/2020A1211

 

Sample Output

 

CategoryMax Value for past year
A1233

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

October NL Carousel

Fabric Community Update - October 2024

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