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

## Dynamic Top N by Fiscal Year

I am trying to create a Dynamic slicer for all my charts and tables on my power bi dashboard so the user can enter in a TopN for Fiscal Year and it will filter the charts so that it shows the top fiscal years. So for example if a user wanted to see the last 10 fiscal years in the bar chart, they would enter 10 in the slicer and it will give them their desired results. I have tried to follow along to the below article and have created a Top N parmater but im getting stuck after that. Note sure how I can tie the Top N to my Fiscal Year I am using in my tables and charts.

1 ACCEPTED SOLUTION
Super User

@ROCKYDO12 , if you have not selected a year

This year Today =
var _max = maxx(allselected('Date'), 'Date'[FY] )  //assume FY is number else have Rank on FY

var _min = _max -10
return
CALCULATE([Net], FILTER('Date','Date'[FY] >=_min && 'Date'[FY] <= _max))

rank column

Year Rank = RANKX(all('Date'),'Date'[FY Year Start date],,ASC,Dense)

Measure =

var _max = maxx(allselected('Date'), 'Date'[Year Rank] )  //assume FY is number else have Rank on FY

var _min = _max -10
return
CALCULATE([Net], FILTER('Date','Date'[Year Rank] >=_min && 'Date'[Year Rank] <= _max))

In case you have selected year then you an independent table for slicer

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[FY])
var _min = _max -10
return
calculate( sum(Table[Value]), filter('Date', 'Date'[FY] >=_min && 'Date'[FY] <=_max))

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

Super User

@ROCKYDO12 , if you have not selected a year

This year Today =
var _max = maxx(allselected('Date'), 'Date'[FY] )  //assume FY is number else have Rank on FY

var _min = _max -10
return
CALCULATE([Net], FILTER('Date','Date'[FY] >=_min && 'Date'[FY] <= _max))

rank column

Year Rank = RANKX(all('Date'),'Date'[FY Year Start date],,ASC,Dense)

Measure =

var _max = maxx(allselected('Date'), 'Date'[Year Rank] )  //assume FY is number else have Rank on FY

var _min = _max -10
return
CALCULATE([Net], FILTER('Date','Date'[Year Rank] >=_min && 'Date'[Year Rank] <= _max))

In case you have selected year then you an independent table for slicer

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[FY])
var _min = _max -10
return
calculate( sum(Table[Value]), filter('Date', 'Date'[FY] >=_min && 'Date'[FY] <=_max))

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors