Last 12 Months Ranking based on filtered value

[phjz]

Hey guys,

is it possible to extract the related date value from a filtered field to show a ranking within the last 12 months from this given date?

Let's say I have a table called "table1" and in this table is a column called "title" and a column called "date". Within the report, I want to use this title column in a slicer. In the same report page, I want to have a ranking table visual with titles and a KPI measure. I want the ranker to show the top 20 titles (based on the KPI measure) within the last 12 months, starting from the related date value from the filtered title.

But when I do this without further DAX, the ranker only shows the filtered entry. Is there a possibility to do that?

Thanks!

[bhanu_gautam · Accepted solution]

@phjz Ensure you have a date table in your model. If not, create one.

Create a measure that calculates your KPI.

Create a measure that calculates the ranking based on the KPI measure within the last 12 months.

DAX
KPI Measure =
-- Replace this with your actual KPI calculation
SUM('table1'[KPI])

Ranking Measure =
VAR SelectedDate = MAX('table1'[date])
VAR StartDate = EDATE(SelectedDate, -12)
VAR FilteredTable =
FILTER(
ALL('table1'),
'table1'[date] >= StartDate &&
'table1'[date] <= SelectedDate
)
RETURN
RANKX(
FilteredTable,
[KPI Measure],
,
DESC,
DENSE
)

Add a slicer to your report using the title column.

Add a table visual to your report and include the title, date, and the Ranking Measure.

Apply a filter to the table visual to show only the top 20 titles based on the Ranking Measure

[v-ssriganesh]

Hi @phjz,

May I ask if you have resolved this issue? If so, please mark it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-ssriganesh]

Hi @phjz,
Could you please confirm if your query have been resolved by the solution provided by? If so, please mark it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[phjz]

Has anyone an idea? The measure provided by @bhanu_gautam works fine, but the slicer within the report still filters the whole table, so hat it shows only the filtered title...

Thanks!

[v-ssriganesh · Accepted solution]

Hi @phjz,

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to @bhanu_gautam for sharing valuable insights.

After thoroughly reviewing the details you provided, When you use a slicer, it applies a filter directly to the visual. Visual-level filters take precedence and override any broader filtering logic you might define in a measure or calculated table, this will make the results limited to slicer selection.

If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[v-ssriganesh]

Hi @phjz,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.
Thank you.

[bhanu_gautam · Accepted solution]

@phjz Ensure you have a date table in your model. If not, create one.

Create a measure that calculates your KPI.

Create a measure that calculates the ranking based on the KPI measure within the last 12 months.

DAX
KPI Measure =
-- Replace this with your actual KPI calculation
SUM('table1'[KPI])

Ranking Measure =
VAR SelectedDate = MAX('table1'[date])
VAR StartDate = EDATE(SelectedDate, -12)
VAR FilteredTable =
FILTER(
ALL('table1'),
'table1'[date] >= StartDate &&
'table1'[date] <= SelectedDate
)
RETURN
RANKX(
FilteredTable,
[KPI Measure],
,
DESC,
DENSE
)

Add a slicer to your report using the title column.

Add a table visual to your report and include the title, date, and the Ranking Measure.

Apply a filter to the table visual to show only the top 20 titles based on the Ranking Measure

[phjz]

Thank you so much for your quick reply!

I implemented your measure and the rankx gives me the right ranking per title. But there is still one problem: When I choose a title with my slicer, it still filters the whole table so that I only can see the filtered title and its ranking. But I still want to see all top 20 titles. Is this possible?

Thanks!