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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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