# DAX – Top N for each Previous & Current Year

### DAX – Top N for each Previous & Current Year

#### Requirement:

Show the sales of the top 5 countries for the current year and previous year.

#### Challenges:

The above requirement seems to be simple, but we don’t have the same customers on the previous and current year.

1. If we use a simple bar/column chart, then we will get multiple standalone bars or columns.
2. We can use a stacked column chart but if we apply the top 5 then it will apply on the whole dataset, but we need to apply for each year.

#### Solution:

1. Create a DAX table with below logic
1. TOP 5
2. Filter – Current Year & Previous Year
3. Union the above results

#### Detailed Steps:

I am using below dataset (Sales).

 Country Year Value A 2018 12 B 2018 34 C 2018 56 D 2018 17 E 2019 17 F 2019 19 G 2019 11 H 2019 23 I 2017 16 J 2017 18 K 2017 23 L 2017 26 M 2017 56

Used the DAX syntax to create another table

_Top5 = UNION(

CALCULATETABLE(

TOPN(5,

SUMMARIZE(Sales,Sales[Year],Sales[Country],Sales[Value])

,Sales[Value], DESC),

Sales[Year]=FORMAT(YEAR(TODAY()),”0000″)),

CALCULATETABLE(

TOPN(5,

SUMMARIZE(Sales,Sales[Year],Sales[Country],Sales[Value])

,Sales[Value], DESC),

Sales[Year]=FORMAT(YEAR(TODAY())-1,”0000″))

)

Now, create a stacked column chart which will give the expected result.

post your comment, if you have any other approaches.