Forum Discussion

ROCKYDO12's avatar
ROCKYDO12
Icon for Helper III rankHelper III
4 years ago
Solved

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 fis...
  • amitchandak's avatar
    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 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