Show historical trend based only on latest month's data

[avalbuena804]

Hello, I have a line chart that I need to display in a special way. Here's some sample data that matches what my current data looks like:

I have a top N filter on category, where I want to show the top 3 categores by sum of cost, and Power BI has done that. What I want to do is show the top N by category by sum of cost, but that sum should only calculate based on the latest month. In other words, I need to show the top 3 categories by sum of cost in the latest month of my data, and then show the historical trendlines behind that. In the picture, Mangoes is -25, where the correct value should show positive data only. The correct line chart should show Grapes instead of Mangoes, as they were the top 3 cost in February 2025 (the latest month of my data).

[johnbasha33 · Accepted solution]

@avalbuena804 
Hi, You need to adjust your Top N filter so that it ranks categories based on the latest month's cost rather than the total cost across all months. Then, once you've identified the top 3 categories, you can display their historical trends.

Latest Month =
CALCULATE(
MAX(Orders[MonthYear]),
ALL(Orders)
)

Create a Measure for Cost in the Latest Month

This measure calculates the cost only for the latest month while ignoring filters on other months.

Latest Month Cost =
VAR _LatestMonth = [Latest Month]
RETURN
CALCULATE(
SUM(Orders[Cost]),
Orders[MonthYear] = _LatestMonth
)

Create a Ranking Measure for Top N Based on Latest Month

Now, we rank the categories based on their cost in the latest month.

Category Rank =
VAR _Rank =
RANKX(
ALLSELECTED(Orders[Category]),
[Latest Month Cost],
,
DESC,
DENSE
)
RETURN
IF(_Rank <= 3, _Rank)

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[avalbuena804]

This worked! Thank you so much!