Forum Discussion

Happy_yeoh's avatar
Happy_yeoh
Frequent Visitor
2 years ago

Top n filter based on latest date

Dear All, 

I can show the top 5 products based on the revenue of the latest date over the last few weeks in Matrix (Power BI). Unfortunately, it cannot display in the line Chart.

Please advise how can I show the top 5 products based on the revenue of the latest date over the last few weeks in Line Chart (Power BI) which same as Matrix. 

 


Latest Date = MAX(Calendar_Date[Date])

 

Rank by Revenue on Latest Date =
VAR LatestDate = [Latest Date]
RETURN
RANKX(
ALL(Revenue[Product]),
CALCULATE(
SUM(Revenue[Revenue EUR]),
'Revenue'[Date]= LatestDate
),
,
DESC,
DENSE
)

Need Help
Show and Tell

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    Happy_yeoh , Try 2 measures like

     

    Latest Revenue =
    VAR LatestDate = maxx(allselecetd(Calendar_Date) , Calendar_Date[Date])
    RETURN
    CALCULATE(
    SUM(Revenue[Revenue EUR]),
    filter(Calendar_Date, Calendar_Date[Date]= LatestDate
    ))

    Top 5 Revenue on Latest Date =
    calculate([Latest Revenue]
    KEEPFILTERS( TOPN( 5, ALLselected(Revenue[Product]),[Latest Revenue],DESC
    )))

    • Happy_yeoh's avatar
      Happy_yeoh
      Frequent Visitor
      2 years ago

      I followed the DAX provided by you, but the outcome is not what I want. I want to show all dates instead of the latest date in the chart.

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    2 years ago

    Hi Happy_yeoh - To display the top 5 products based on the revenue of the latest date over the last few weeks in a Line Chart in Power BI.

     

    I have created below measure to get the latest date

    LatestDate = MAX(Calend[Date])
     
    Created another measure to display the rank the products based on their revenue on the latest date
     
    RankByReveOnLatestDate =
    VAR LatestDate = [LatestDate]
    RETURN
    RANKX(
        ALL(Reve[Product]),
        CALCULATE(
            SUM(Reve[Revenue EUR]),
            Reve[Date] = LatestDate
        ),
        ,
        DESC,
        DENSE
    )
     
    last calculation to filter the visual to diplay the top 5 products based on the ranking you created
     
    Top5Filter =
    IF(
        [RankByReveOnLatestDate] <= 5,
        1,
        0
    )
     

     

    I have set the filter to show only where Top5Filter is equal to 1
     

    Hope it works, please check

     
    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!
    • Happy_yeoh's avatar
      Happy_yeoh
      Frequent Visitor
      2 years ago

      I followed your DAX. However, the outcome is the same as mine, showing the top 5 products for every week differently. I want to show the top 5 products based on the latest date for every week. The matrix shows the top 5 products ranked based on the latest week's top 5 products and applies this ranking to the last few weeks. You can see that some products were ranked 8th on May 19 and 26, and 5th on June 2.