cancel
Showing results for
Did you mean:

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

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