Forum Discussion
Dynamic Top N by Fiscal Year
- 4 years ago
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 FYvar _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
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