How to do TOP N in a period of time

[Migscruz]

Hi! How can i do a TOP N in a period of time? I have the sales from january 2020 to november 2020 and i want to see the top 5 products in a line chart. The X axis should be all the year 2020 (from january 2020 to november 2020) but i need to show me only the top 5 products sales in the last 3 months.

Thanks 🙂 

[v-yuaj-msft]

Hi @Migscruz ,

Based on your description, you can do some steps as follows.

1. Create a calculated column.

Column = IF(MONTH('Sheet3'[date])<9,0,'Sheet3'[Value])

1. Create a line chart.

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat · Accepted solution]

Please try this measurement expression to get your result. You can set the January and Novon date range with a slicer, but this will limit the lines shown to the top3 in the last 3 months (assuming you have the product column as a legend).

Keep the 3 best products ?
VAR maxdate ?
MAX ( 'Date'[Date] )
VAR top3 ?
TOPN (
3,
ALL ( 'Product'[Product]) , )
CALCULAR (
[Total Sales],
EVERYTHING ( 'Date' ),
'Date'[Date] <-maxdate,
'Date'[Date]
> EDATE (
maxdate,
-3
)
)
)
Return
CALCULAR (
[Total Sales],
KEEPFILTERS ( top3 )
)

Best regards

Pat

[themistoklis]

@Migscruz 

You can either use the Filters pane add the measure and the select top X products based on this measure.

Or you can use a measure like the following one:

Top 2 Products per Region =
VAR
ranking = VALUES(‘Sales Records'[Item Type])
Return
CALCULATE([Total Revenue],
TOPN(2,ALL(‘Sales Records'[Item Type]),[Total Revenue]),
ranking)

[Migscruz]

Hi @themistoklis ! But i need in the X axis to show me all the period of 2020 of just the 5 products with more sales in the last 3 months. If i use the filter then it shows me the top 5 products in the period of all 2020.

Thanks 🙂