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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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