Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
ROCKYDO12
Helper III
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.

 

https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...

 

1 ACCEPTED SOLUTION
amitchandak
Super User
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
https://www.youtube.com/watch?v=km41KfM_0uA

View solution in original post

1 REPLY 1
amitchandak
Super User
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
https://www.youtube.com/watch?v=km41KfM_0uA

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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