March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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.
Solved! Go to 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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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
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
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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 |
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
89 | |
85 | |
73 | |
49 |
User | Count |
---|---|
169 | |
144 | |
90 | |
70 | |
58 |